problems converting dates as varchar to datetime data type

Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

In what format do you have your dates at the moment?

You can try using SET DATEFORMAT dmy before running the statment.

Or look at using CONVERT with the relevant style.

eg CONVERT(DATETIME, YourDateField, 103)

Check Books Online for more details

HTH!

|||Try CONVERT function, make sure the style parameter matches your varchar date format
|||

If your date is in a recognizable format SQL Server uses an implicit conversion from varchar to datetime, so no CONVERT or CAST function is necessary. If the date is in an unrecognizable format you can use the T-SQL string functions (listed in BOL) to reconstruct it into a recognizable format and you should get no errors.

|||

Hi,

I will post some more information about my problem.

In the source DB i have the "Beginning_Date" column, as varchar, with values with this format "dd/mm/yyyy HH.MM.SS".

In the target DB i have the "Beginning_Date" column again, but must be only with the the date part (dd/mm/yyyy) as datetime type, and the "Beginning_Time" column, storing the time part (HH:MMS) as nchar type.

I have no problems with the time part of the records, but the date part always fail.

I use the substring function in order to get only the date part of the records and try to insert it to the target DB both with "dd/mm/yyyy" and "mm/dd/yyyy" date formats. I always get the same error (out of range). I have tried also the "set datetime dmy" .......

Thanks for your help,

Bob

|||
Are all your dates in Beginng_Date in the same format? Are you sure there isn't a rogue value in there which is causing the whole statement to crash?

Can you post your DDL and DML statements and some sample data for us to help troubleshoot further?

I had a guess at what you were trying to do:

Code Snippet

CREATE TABLE tblDateTime
(d1 DATETIME, t1 NCHAR(10))

DECLARE @.String NVARCHAR(30)
SET @.String = '21/12/2007 14:15:15'

SET DATEFORMAT dmy

INSERT INTO tblDateTime
SELECT SUBSTRING(@.String, 1, 10), REVERSE(SUBSTRING(REVERSE(@.String), 0, 9))

Is this anything like what you're doing?

|||

Hi,

The problem is solved now.

richbrownesq, you were right. My problem was only one (is enough...) record wrong.

Thanks a lot for ALL!!!!