Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Reformat of Date and Time? Please!!

Status
Not open for further replies.

RJ Jackson

Technical User
Jul 24, 2018
12
US
Driving me crazy! What am I missing?

FileName: Whatever1_Whatever2_Whatever3_20180716143004
Initial Value: 20180716143004
Expected Date Result: 07/16/2018 using varDOS
Expected Time Result: 14:30:04 using varTOS

Here's what I got below. Basically trying to take that long value, and get the date and time in the above format and they'll store to two different fields.

Function GetDOS(text)
GetDOS = Field (text,"_",4)
End Function

Function ReformatDOS(YMD)
ReformatDOS = Mid(YMD,1,2) & "/" & Mid(YMD,3,2) & "/" & Mid(YMD,5,4)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
End Function

Function ReformatTOS(YMD)
ReformatTOS = Mid(text,1,2) & ":" & Mid(test,3,2) & ":" & Mid(YMD,5,4)
End Function

-----Separate Application that is calling the VBScripts above; Should be good as I'm just getting the value from VB and store it as an index field in an app.------------

//Get Date of Service from FileName
SET varDOS SCRIPT GetDOS varFileName
SET varDOSF SCRIPT ReformatDOS varDOS
STORE varDOSF "DATE_OF_SERVICE"
//Get Time of Service from FileName
SET varTOS SCRIPT GetTOS varFileName
SET varTOSF SCRIPT GetTOSF varTOS
STORE varTOSF "TIME_OF_SERVICE
 
Hi,

GetDOS and GetTOS are returning identical results and likwise ReformatDOS and ReformatTOS return identical results: You’ll get a DATE from both.

You don’t need GetTOS. Your ReformatTOS just needs different arg1 values in the MID() function.

The second thing is that a Date is not a string value. Use CDate()

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Skip,
Dang it...yeah, not supposed to be that way. So, I need the TOS to be (time). I'll get that changed, duh! But, I believe the key here is that I would need to parse out the relevant digits right? So, from 20180716143004...I need to parse the first 8 digits for the date and then get that value reformated and the last 6 digits (or starting at the 9th digit) for the time. Is that what I'm missing is some type of TRIM?
 
So, I actually updated as the below aside from using time cause I got what you meant that I should change the arg.

Function GetDOS(text)
GetDOS = Field (text,"_",4)
End Function

Function ReformatDOS(YMD)
ReformatDOS = Mid(YMD,1,2) & "/" & Mid(YMD,3,2) & "/" & Mid(YMD,5,4)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
End Function

Function ReformatTOS(text)
ReformatTOS = Mid(text,1,2) & ":" & Mid(text,3,2) & ":" & Mid(text,5,4)
End Function
 

Code:
'
    dat = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
    dat = dat & " " & Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
    YourDateTime = CDate(dat)

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
I'm assuming I would use the above and just store then to 2 different variables since they are being indexed into 2 different fields (like excel)?

date = dat
time = Mid(text,9,2) & .........
???

20180716143004 should be 07/16/2018 as 1 value and 14:30:04 as the other value.
 
Sure.

But you’ve got to CONVERT your Strings to Date/Time.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Function GetDOS(text)
GetDOS = Field (text,"_",4)
GetDOS = CDate(GetDOS)
End Function

I feel like I'm missing the mark.
 
Code:
Function GetDOS(text)
GetDOS = Field (text,"_",4)
GetDOS = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
GetDOS = CDate(GetDOS)
End Function

Function GetTOS(text)
GetTOS = Field (text,"_",4)
GetTOS = Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
GetTOS = CDate(GetTOS)
End Function

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Oh you're freaking amazing!!! Wow...like I had the right idea. Much appreciated.
 
Skip, unfortunately, above is pulling the current system date and time. It's not taking the value, parsing, and reformatting. Any other ideas?

String: 20170716134008

I tried....

Function GetDOS(theDate)
theDate = Field (text,"_",4)
theDate = Mid(theDate,5,2) & "/" & Mid(theDate,7,2) & "/" & Mid(theDate,1,4)
GetDOS = theDate
'GetDOS = CDate(GetDOS)
End Function

---separate script, not VBScript, calling the VBScript above--
//Get Date of Service from FileName
SET varDOS SCRIPT GetDOS varFileName
STORE varDOS "DATE_OF_SERVICE"

So, shouldn't this have the variable varDOS go to VBScript GetDOS and pull 20170716 and then 07 16 2017 and place / in between to make 07/16/2017. Then come back to non VBScript and store 07/16/2017 as the value?
?? I referenced GetDOS as theDate so that in another program I'm calling GetDOS as the value to input?
 
Code:
text = "Whatever1_Whatever2_Whatever3_20180716143004"

Function GetDOS(text)
text = Field (text,"_",4)
text = Mid(text, 1, 4) & "/" & Mid(text, 5, 2) & "/" & Mid(text, 7, 2)
GetDOS = CDate(text)
End Function

text = "Whatever1_Whatever2_Whatever3_20180716143004"

Function GetTOS(text)
text = Field (text,"_",4)
text = Mid(text, 9, 2) & ":" & Mid(text, 11, 2) & ":" & Mid(text, 13, 2)
GetTOS = CDate(text)
End Function

Sorry for trying to do too much in too little time.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
BTW, have not seen your “comment” from the link I posted last night.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Skip!!!!! In my world "text" = "varFileName" from my other XDS script. Dude...freaking right on! I totally appreciate it! So working right now! Basically, I replaced text with varFileName 'cause I turned my screen sideways and I finally picked up what you were putting down!

I clicked on the link and it said it was an error.
 
I must have been sleeping or I’m getting senile (not viewing a river in Egypt)

Click on this FAQ link, scroll down, click on Send a comment to SkipVought link. This will send an eMail to me with your eMail address. In turn, I’ll send you an eMail and we’ll go from there. I don’t IM.
Faq707-4594

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top