Date check

[devsonali]

Hi
I have a time stamp field that is in YYYYMMDD HH24:MI:SS format ,
I need to figure out a way to check put a logic where in I need to check if the time is between 12 to 12 59 am then I need to substract one day from the date and then an hour
(for eg if the incoming file has the field as 20081109 12:30:00 then my output needs to be 11 30 pm of nov 8 )
else just substract one hour from the time
if the time is say 3 am of nov 9 then it becomes 2 am same day,
I tried i conv function but looks like i am not doing it right as I am not getting right result set, I am also concerned about dates like 28 and 29 th feb (leap years)

Any help would be greatly appreciated
Thank you

[ArndW]

I will assume that your incoming field is type VarChar() or Char(19). In that case

stage variable
svField = StringToTimeStamp(In.Field,'YYYYMMDD HH:NN:SS')

Derivation
IF In.Field[10,2]='12' THEN TimestampFromSecondsSince((-1*(60*60)+(60*60*24)),svField) ELSE TimestampFromSecondsSince((-60*60),svField
This just subtracts seconds from the timestamp. One day is 60*60*24 seconds and one hour is 60*60 seconds.

[chulett]

Server job.

[ArndW]

Oops, here's a server variation on the same theme. I would normally just stick with string date representations

stage variable
svFieldDate = ICONV(In.Field[1,10],"D4YMD")
svFieldTime = ICONV(In.Field[12,8],"MTS")

Derivation
IF In.Field[10,2]='12' THEN OCONV(svFieldDate-1,'D4YMD'):' ':OCONV(svFieldTime-3600,'MTS') ELSE OCONV(svFieldDate,'D4YMD'):' ':OCONV(svFieldTime-3600,'MTS')

[devsonali]

Thanks ard

I checked this with a test job with input 20081109 12:30:00 and got 2008 11 08 -00:21:00 as out put is this because we are going by 24 hour clock and checking 12 means noon which would be the same day

Should we be checking 00 in "IF In.Field[10,2]='12' "
also , since we are going one hour back, i was wondering wasn t it suppose to show us
1 11:30:00 instead of -00:21:00 (for 12: 30 pm)
and
2 23:30:00 for say 12:30 am

Please advice
Thanks in advance

[ArndW]

The stage vars should be [1,8] and [10,8] respectively, I miscalculated.

[devsonali]

and In.Field[10,2]='12' ?
Does this need to be 12 or 00 ?
Please advice

[ArndW]

'12', per your logic.

[devsonali]

hi arnd

I tried with 20081101 00:40:00 and it gave me 2008 11 01 11:39:00
I was expecting oct 31 instead of nov 1?
Is there a way to handle it

[xcb]

All the details of what you need have been provided in the preceeding posts, if the derivation is wrong you can have a go at changing it yourself to try and get the output your after. If you're unsure on what the functions are have a read of the Basic reference manual, it will tell you how the iconv/oconv functions work.

Having said that, based on your original requirement I think you need:

Derivation
IF svFieldTime < 3600 THEN OCONV(svFieldDate-1,'D4YMD'):' ':OCONV(svFieldTime-3600,'MTS') ELSE OCONV(svFieldDate,'D4YMD'):' ':OCONV(svFieldTime-3600,'MTS')