Page 1 of 2
Date conversion to SQL server
Posted: Tue Feb 27, 2007 6:58 pm
by 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
Posted: Tue Feb 27, 2007 7:32 pm
by ray.wurlod
And what format, precisely, does a DATETIME require?
Posted: Tue Feb 27, 2007 7:34 pm
by 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.
Posted: Wed Feb 28, 2007 1:44 am
by DSguru2B
Look into ICONV/OCONV functions. You need to seperate the date and time part. Appy these functions seperately and concatenate them together.
fmt specified for conversion
Posted: Thu Mar 08, 2007 12:17 pm
by 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
Posted: Thu Mar 08, 2007 12:59 pm
by 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.
Posted: Thu Mar 08, 2007 1:22 pm
by pavanns
i am getting an error cannot convert varchar to datetime null passed
Posted: Fri Mar 09, 2007 6:33 pm
by 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
Posted: Fri Mar 09, 2007 6:59 pm
by 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")
Posted: Fri Mar 09, 2007 11:55 pm
by kumar_s
Reas it as Char and pass it as char. In SQL Server (ODBC) stage, use
.
Here the code corresponds to the given format. Which usually in the ranges like 100, 101, 102... for each format.
Posted: Sun Mar 11, 2007 2:09 pm
by shilpa79
DSguru2B wrote: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.
Posted: Sun Mar 11, 2007 4:04 pm
by ray.wurlod
It is the input field column name, qualified by the name of the input link.
Posted: Mon Mar 12, 2007 1:18 pm
by shilpa79
ray.wurlod wrote: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.
Posted: Mon Mar 12, 2007 1:20 pm
by 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.
Posted: Mon Mar 12, 2007 1:52 pm
by shilpa79
shilpa79 wrote:DSguru2B wrote: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")