Date conversion

[MW4]

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

[JTaylor]

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

[MW4]

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.

[MW4]

12/13/2014 4:26 PM
to
12/13/2014 2:26 PM

[JTaylor]

I meant do it as part of the Insert/Update operation that occurs in WinBatch.

Jim

[MW4]

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   

[JTaylor]

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

[MW4]

Access

[stanl]

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

[JTaylor]

This should do what you need, with the assumptions I previously mentioned...

DateAdd('h',-2,"12/13/2014 4:26 PM")


Jim

[MW4]

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.

[JTaylor]

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

[stanl]

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"))

[MW4]

Yes, but still need to convert it back...

I went the brute force method...

Code (winbatch) Select Expand

;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))

[td]

Stan's suggestion works in either direction.

Code (winbatch) Select Expand

vtDate = ObjectType("DATE","12/13/2014 11:01 AM")
strWilDate = vtDate:""
Message("WIL Date", strWilDate)

or

Code (winbatch) Select Expand

vtDate =  ObjectType("DATE","2014:12:13:16:26:00")
strDate = ObjectType("bstr", vtDate)
Message("Slash  Date", strDate)


[MW4]

I'll give that a try...THANKS!!!