How to change TimeStamp Format

imfarhan · Post by **imfarhan** » Tue Oct 16, 2012 5:05 am

Hi
I think I have a similar issue; need to change the Timestamp format

The actual source like this SpecimenDate = 31-Jan-12
So i've convert using following function :

Code: Select all

if NullToEmpty(InfectCon_Patients.SPECIMEN_DATE) = "" then setnull() else if IsValid("date",InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%yy") then StringToTimestamp(InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%yy") else setnull()

The type is TIMESTAMP LENGHT 26 Scale = 6

on DS output : i'm getting like : 1912-01-31 00:00:00
and the Output on Table I'm getting like : mm/dd/yy 12:00AM

Don't know why
I like to see the output like YYYY-MM-DD 00:00:00

Thanks for your help!
F

chulett · Post by **chulett** » Tue Oct 16, 2012 5:34 am

Since you didn't supply a time, then either "00:00:00" or "12:00AM" should be expected. What it looks like on output is all about your SELECT statement and the TO_CHAR() format string you (or your tool) is using. There's nothing wrong with what you've stored in the table.

imfarhan · Post by **imfarhan** » Tue Oct 16, 2012 5:37 am

But Why I'm getting 1912 instead of "2012"
Do I've to change the format or Timestamd if yes how?
Regards
F

chulett · Post by **chulett** » Tue Oct 16, 2012 5:52 am

Oh, that.

Open up the Parallel Job Developer's Guide pdf and search for year_cutoff which specifies the century pivot for two digit years. It defaults to 1900 which is why you see the behaviour you are seeing but you can override it in the format string.

imfarhan · Post by **imfarhan** » Tue Oct 16, 2012 7:19 am

Step(1)The svSpecDateConv =

Code: Select all

if NullToEmpty(InfectCon_Patients.SPECIMEN_DATE) = "" then setnull() else if IsValid("date",InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%yy") then StringToDate(InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%yy") else setnull()

svSpecDateConv is the stage-variable and type is 'date'

as the SpecDate field showing 1900 for years

Step(2)Add 100 years to convert year 1900 then change to "Timestamp" using the function TimestampFromDateTime as shown below:

Code: Select all

TimestampFromDateTime(DateOffsetByComponents(svSpecDateConv,100,0,0), '00:00:00')

Thanks for your help
Regards
F

chulett · Post by **chulett** » Tue Oct 16, 2012 7:56 am

So you completely ignored my advice... interesting. What happens when your two digits come in as "97"?

chandra.shekhar@tcs.com · Wed Oct 17, 2012 1:00 am

If you are very sure that your all the years would be of 21st century i.e. starting with 20 Then you can use a simple formula instead of your 2 step method :

Code: Select all

if NullToEmpty(InfectCon_Patients.SPECIMEN_DATE) = "" then setnull() else if IsValid("date",InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%yy") then StringToDate(InfectCon_Patients.SPECIMEN_DATE,"%dd-%mmm-%2000yy") else setnull().