Date conversion to SQL server

[pavanns]

Hi I have a VARCHAR field in the source that holds date in 1/11/2007 7:30:00 PM
I need to convert this into timestamp in the target can you please comment

Please note that the allowed format in SQL server is DATETIME

[ray.wurlod]

And what format, precisely, does a DATETIME require?

[urshit_1983]

you can use :-

TO_DATE(char[,fmt])

So as Ray mentioned in "fmt" use the format you need for eg :

TO_DATE('25-JUN-1952 10:55 A.M.','DD-MON-YYYY HH:MI A.M.')

In Transformer use Timestamp as SQL type for this field.

[DSguru2B]

Look into ICONV/OCONV functions. You need to seperate the date and time part. Appy these functions seperately and concatenate them together.

[pavanns]

i need it in the same format as input it is like 13/09/2007 11:00:00 AM

the input is from an xml and i tried even getting it from sequential file but both SQL types are varchar. i need the o/p on SQL SERVER also in the same format but as a DATETIME sql type. pls help

I tried doing to_date it is not woorking

[ray.wurlod]

If you need the format the same as input, declare it as VarChar throughout your job and do not apply any conversion at all.

[pavanns]

i am getting an error cannot convert varchar to datetime null passed

[shilpa79]

I have the similar isuue:

I have to convert string to Timestamp
sample input date: " 1/10/2007 2:19:16 PM "

Thanks in advance

[DSguru2B]

Does'nt SQL Server take timestamp in YYYY-MM-DD for date and MTS format for time?
Does the following work for you guys

Code: Select all

OCONV(ICONV(Field(in.DATE, " ", 1), "D/DMY"),"D-YMD[4,2,2]):" ":OCONV(ICONV(Field(in.DATE," ",2), "MT"), "MTS")

[kumar_s]

Reas it as Char and pass it as char. In SQL Server (ODBC) stage, use

Code: Select all

convert(char,Input.field,code)

.
Here the code corresponds to the given format. Which usually in the ranges like 100, 101, 102... for each format.

[shilpa79]

Does'nt SQL Server take timestamp in YYYY-MM-DD for date and MTS format for time?
Does the following work for you guys

Code: Select all

OCONV(ICONV(Field(in.DATE, " ", 1), "D/DMY"),"D-YMD[4,2,2]):" ":OCONV(ICONV(Field(in.DATE," ",2), "MT"), "MTS")

in.DATE is a function or the Input date field column.

[ray.wurlod]

It is the input field column name, qualified by the name of the input link.

[shilpa79]

It is the input field column name, qualified by the name of the input link. ...

Do I need to change the sql type from Timestamp to varchar because its not loading anything if its Timestamp.

[DSguru2B]

Try changing it. Also, since this is such a struggle, load it to a flat file, just a few records to see how it looks like. It should have two parts. The first is date in the format YYYY-MM-DD and second is time in the format HH:NN:SS. Both parts should have a single space in between them.

[shilpa79]

Does'nt SQL Server take timestamp in YYYY-MM-DD for date and MTS format for time?
Does the following work for you guys

Code: Select all

OCONV(ICONV(Field(in.DATE, " ", 1), "D/DMY"),"D-YMD[4,2,2]):" ":OCONV(ICONV(Field(in.DATE," ",2), "MT"), "MTS")

in.DATE is a function or the Input date field column.

I have given the same format still i am not able to load the date.

Oconv(Iconv(xo_dailysalestrans.TransTimestamp, " ", 1), "D/DMY"),"D/YMD[4,2,2]):" ":
Oconv(Iconv(xo_dailysalestrans.TransTimestamp," ",2), "MT"), "MTS")