Date check
Moderators: chulett, rschirm, roy
Date check
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
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
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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')
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')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
The stage vars should be [1,8] and [10,8] respectively, I miscalculated.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
'12', per your logic.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 66
- Joined: Wed Mar 05, 2003 6:03 pm
- Location: Brisbane, Australia
- Contact:
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')
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')
Cameron Boog