Date conversion to SQL server

shilpa79 · Post by **shilpa79** » Mon Mar 12, 2007 1:54 pm

DSguru2B wrote: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.

I tried to load into the Flat file with Timestamp format , its loading perfectly

shilpa79 · Post by **shilpa79** » Mon Mar 12, 2007 1:58 pm

DSguru2B wrote: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.

I tried to load into Flat file with sqltype Timestamp sucessfully

DSguru2B · Post by **DSguru2B** » Mon Mar 12, 2007 2:16 pm

Did you spot check a few fields in the flat file. Is the conversion correct?. Also in my initial input, ICONV for time should be "MTHS" instead of "MT" This conversion will only be successful if there is no space between seconds and AM/PM. If there is space you need to get rid of that space then.

us1aslam1us · Post by **us1aslam1us** » Mon Mar 12, 2007 5:22 pm

While Searching the forum I found this post
viewtopic.php?t=102661&highlight=

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 9:02 pm

Once you read it as Varchar, try to maintain it as Varchar till your target stage and convert it back to Data format.
Do you get any error or warning in current scenario?

us1aslam1us · Post by **us1aslam1us** » Tue Mar 13, 2007 12:33 pm

Hi Kumar, I tried that part at my end but the issue seems to be with time part, But waiting for OP's response.

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 5:01 pm

us1aslam1us wrote:Hi Kumar, I tried that part at my end but the issue seems to be with time part, But waiting for OP's response.

Did you used Convert()? May I know the code that you used? I too lazy to search for that.

pavanns · Post by **pavanns** » Tue Mar 13, 2007 5:36 pm

i tried doing it with varchar all the way but does not work as it says cannot insert null into the column.

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 5:37 pm

pavanns wrote:i tried doing it with varchar all the way but does not work as it says cannot insert null into the column.

As noted, its may be due the datatype mismatch. You cannot load varchar as it is in to a Date field. You need to have a date conversion at the target stage.

DSguru2B · Post by **DSguru2B** » Tue Mar 13, 2007 5:41 pm

pavanns wrote:i tried doing it with varchar all the way but does not work as it says cannot insert null into the column.

Is it possible that your getting null from source and trying to insert to a not null column?
None of the rows get inserted?