Convert CHAR to DATETIME

[Stokesy]

Hi,

I'm currently trying to convert a character into a datetime but I keep getting the error "resulted in an out-of-range datetime value."

The character value I have is in the format "27-feb-2003 09:00:00"

I have tried different parameters in the CONVERT command but cannot seem to find the correct one.

Any ideas?

Thanks,
James

 

[collierd]

Think it may be something to do with the date format
I had a similar problem and got round it by writing a function which converted each part of the character string then put it all back together to form a varchar string in the form yyyy-mm-dd which could be converted to a smalldatetime type

Damian

 

[Zathras]

This script runs ok in Query Analyzer (conversion is automatic):

declare @MyDate as datetime
select @MyDate = '27-feb-2003 09:00:00'
select @MyDate as MYDATE

Are you using double or single quotes?

 

[Stokesy]

I dont want to hard code the date into my SQL.

Any way I can keep the format of the date (dd-mmm-yy t) yet use it as a parameter in a convert command???

 

[Zathras]

You don't need to use the convert command. Just format the date/time as a string and use it. SQL Server makes the conversion automatically.

 

[tlbroadbent]

You can use the style parameter when converting a string to datetime.

convert(datetime, '27-feb-2003 09:00:00', 113)

Declare @chardate varchar(24)
Set @chardate='27-feb-2003 09:00:00'

Select DateValue = convert(datetime, @chardate, 113)
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Stokesy]

Thank you all for your help - its been handy. I have it working now.