Is there any way to handle single digit's in date?

Database
Enthusiast

Is there any way to handle single digit's in date?

Hi All,

I have a date "Mar 1 2013" which i want to load it into a table with date format as 'MMMBDDBYYYY' is there any way i can load this without adding a "0" before the 1 in the given date?

i am using TD14.

Tags (2)
9 REPLIES
Junior Contributor

Re: Is there any way to handle single digit's in date?

In TD14 there's Oracle's TO_CHAR, which is a bit more flexible than TD's FORMAT :-)

to_date('Mar 1 2013', 'mon dd yyyy')

Dieter

Enthusiast

Re: Is there any way to handle single digit's in date?

Thanks Dieter for de promt response :-)

One more doubt, do we have anythign similar to handle the timestamp directly or we need to substring and convert it to proper timestamp?

Enthusiast

Re: Is there any way to handle single digit's in date?

Dieter,

Got the function to_timestamp. But this is the timestamp format i am getting from the source

"May 10 2013 10:58:04:393AM", how can i load this into the TD table with the datatype as timestamp(6)?

 

I have tried like this
SEL TO_TIMESTAMP('May 10 2013 10:58:04:3934AM', 'mon dd yyyy hh:mi:ss:ssssPM')

but not getting the desired OP.





Senior Supporter

Re: Is there any way to handle single digit's in date?

Try SEL TO_TIMESTAMP('May 10 2013 10:58:04:3934PM', 'Month dd yyyy hh:mi:ss:ssssAM')

Junior Contributor

Re: Is there any way to handle single digit's in date?

The format for  fractional seconds in Oracle is FFx, SSSSS is the number of seconds since midnight.

 SEL TO_TIMESTAMP('May 10 2013 10:58:04:3934PM', 'Month dd yyyy hh:mi:ss:FF4AM')

Dieter

Enthusiast

Re: Is there any way to handle single digit's in date?

Thanks Guys :-)

Enthusiast

Re: Is there any way to handle single digit's in date?

Not sure what I am doing wrong here

SEL TO_TIMESTAMP('11/04/2011 11:26:35.345' 'MM/DD/YYYY hh:mi:ss.FF3')

error:  SELECT Failed. 9134:  YYYY value must be four digits and in the range 1-9999 

Junior Contributor

Re: Is there any way to handle single digit's in date?

Simply add a comma ;-)

SEL TO_TIMESTAMP('11/04/2011 11:26:35.345', 'MM/DD/YYYY hh:mi:ss.FF3')

Dieter

Enthusiast

Re: Is there any way to handle single digit's in date?

Thanks Dieter. Learnt a new way rather than using all that substring, position combinations :)