All Forums Database
barani_sachin 141 posts Joined 01/12
03 Jun 2013
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.

dnoeth 4628 posts Joined 11/04
03 Jun 2013

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

Dieter

barani_sachin 141 posts Joined 01/12
04 Jun 2013

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?

barani_sachin 141 posts Joined 01/12
04 Jun 2013

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.

 

ulrich 816 posts Joined 09/09
04 Jun 2013

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
04 Jun 2013

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

Dieter

barani_sachin 141 posts Joined 01/12
04 Jun 2013

Thanks Guys :-)

Harpreet Singh 101 posts Joined 10/11
25 Jun 2013

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 

dnoeth 4628 posts Joined 11/04
25 Jun 2013

Simply add a comma ;-)
SEL TO_TIMESTAMP('11/04/2011 11:26:35.345', 'MM/DD/YYYY hh:mi:ss.FF3')
 
Dieter

Dieter

Harpreet Singh 101 posts Joined 10/11
30 Jun 2013

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

You must sign in to leave a comment.