Date conversion to SQL server
Moderators: chulett, rschirm, roy
Date conversion to SQL server
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
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
pavan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
fmt specified for conversion
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
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
pavan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Does'nt SQL Server take timestamp in YYYY-MM-DD for date and MTS format for time?
Does the following work for you guys
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")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
in.DATE is a function or the Input date field column.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 guysCode: Select all
OCONV(ICONV(Field(in.DATE, " ", 1), "D/DMY"),"D-YMD[4,2,2]):" ":OCONV(ICONV(Field(in.DATE," ",2), "MT"), "MTS")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
shilpa79 wrote:in.DATE is a function or the Input date field column.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 guysCode: Select all
OCONV(ICONV(Field(in.DATE, " ", 1), "D/DMY"),"D-YMD[4,2,2]):" ":OCONV(ICONV(Field(in.DATE," ",2), "MT"), "MTS")
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")