I get date data that is in this format: 12/13/2014 4:26 PM
I would like to subtract two hours before I add it to my database as it is coming from another timezone.
How do I go from readable to YMDHMS, subtract, then back to readable, as it needs to go in just like I get it.
I can find info on YMDHMS to readable, but not in reverse.
Any help will be appreciated
Can you do the math using that format as an SQL operation? Using something like Date_Add, Date_Subtract, DateDiff, etc. Depends on the database but might be an option.
Jim
I add it directly using winbatch, and I'd rather keep the change there.
So I know all data before the database is Central time and within our database it's Pacific.
12/13/2014 4:26 PM
to
12/13/2014 2:26 PM
I meant do it as part of the Insert/Update operation that occurs in WinBatch.
Jim
I can -- IF -- I can get from 12/13/2014 4:26 PM to 2014:12:13:16:26:00
that's what I can't find the answer to.
Getting 2014:12:13:16:26:00 to 2014:12:13:14:26:00
And
Getting 2014:12:13:14:26:00 to 12/13/2014 2:26 PM is well documented.
need this sequence:
12/13/2014 4:26 PM
2014:12:13:16:26:00
2014:12:13:14:26:00
12/13/2014 2:26 PM
You should be able to do that using StrSub() and/or ItemExtract() and probably StrFixLeft(). What I meant though, assuming you are doing the update/insert to the database via SQL and you don't need the YmdHms format for other than the math, is something like:
Insert into table_name (rec_num, name, date) values (123, "joe", DateSubtract(CST_Date, 2));
Syntax/Function for the date math will depend on what database you are using.
Jim
Access
Probably with Access you could use the cDBL() function to convert the date to a number, subtract subtract 2/24 then convert back to a date.
I also work daily with converting MySQL data (which comes as a double) and converting to Mountain Time from EST - using
format(DateAdd("h",-2,B.Start) where B.Start is a timestamp, and the function subtracts 2 hours
This should do what you need, with the assumptions I previously mentioned...
DateAdd('h',-2,"12/13/2014 4:26 PM")
Jim
it gets put into access via:
rs.addnew
rs.fields("ClaimDt").value = StrTrim(StrClean (ClaimDt, @CRLF, "", @TRUE, 1))
rs.update
DateAdd isn't Winbatch, which is where I want the change to happen, not in access.
I already mentioned the functions you could use if you want to convert the date in WinBatch, do the math and then convert it back. I mentioned the other if it was a viable option as it saves all the conversion work.
Jim
If your data is in the format:
d= "2014:12:13:16:26:00"
why not rs.collect("ClaimDt") = ObjectType("DATE",TimeSubtract(d, "0000:00:02:00:00:00"))
Yes, but still need to convert it back...
I went the brute force method...
;convert a human readable time into WinBatch YmdHms format time
;Subtract a time then convert back to a human readable time.
;USA MM/DD/YYYY HH:MM:SS AMPM format
;AAA="12/13/2014 12:01 PM"
;AAA="12/13/2014 12:01 AM"
AAA="12/13/2014 11:01 AM"
gMon= ItemExtract(1,AAA,"/")
gDay= ItemExtract(2,AAA,"/")
if gday < 10 then gday = strcat("0",gday)
gYr = strsub(ItemExtract(1,AAA," "),strlen(ItemExtract(1,AAA," ") )-3,4)
if gmon < 10 then gmon = strcat("0",gmon)
gHour= strsub( ItemExtract(2,AAA," "),1, strlen(ItemExtract(2,AAA," "))-3)
if ghour < 10 then ghour = strcat("0",ghour)
gMins= strsub(ItemExtract(2,AAA," "),strlen(ItemExtract(2,AAA," ") )-1,2)
gord = strsub(aaa,strlen(aaa)-1,2)
if gord == "PM" then
if ghour == 12
ghour=ghour
else
ghour=ghour+12
endif
else
if ghour==12 then
ghour="00"
endif
endif
BaseDt = strcat(gyr,":",gMon,":",gday,":",ghour,":",gmins,":00")
newdate = timesubtract(basedt,"0000:00:00:02:00:00")
xyear= ItemExtract(1,newdate,":")
xmonth=ItemExtract(2,newdate,":")
if strsub(xmonth,1,1)==0 then xmonth = StrFixCharsL(xmonth, 0, 1)
xday= ItemExtract(3,newdate,":")
if strsub(xday,1,1)==0 then xday = StrFixCharsL(xday, 0, 1)
xhour= ItemExtract(4,newdate,":")
xmin= ItemExtract(5,newdate,":")
AMPM="AM"
If xhour>=12 Then AMPM="PM"
If xhour>12 Then xhour=xhour-12
if xhour<10 then xhour = StrFixCharsL(xhour, 0, 1)
if xhour=="0" then xhour = 12
newtime=StrCat(xmonth,"/",xday,"/",xyear," ",xhour,":",xmin," ",AMPM) ;
mes1=strcat("original date",@tab,@tab, aaa,@crlf)
mes2=strcat("converted to YMMDHMS",@tab,basedt,@crlf)
mes3=strcat("Two hours subtracted",@tab,newdate,@crlf)
mes4=strcat("Fixed date",@tab,@tab,@tab,newtime)
Message("dates ", strcat(mes1,mes2,mes3,mes4))
Stan's suggestion works in either direction.
vtDate = ObjectType("DATE","12/13/2014 11:01 AM")
strWilDate = vtDate:""
Message("WIL Date", strWilDate)
or
vtDate = ObjectType("DATE","2014:12:13:16:26:00")
strDate = ObjectType("bstr", vtDate)
Message("Slash Date", strDate)
I'll give that a try...THANKS!!!