string to date

[MsMellow]

Hi,

Does anyone know how to convert 'Oct-09-07' to a date data type?

Thank you!

 

[SQLDenis]

declare @s varchar(30)

select @s ='Oct-09-07'
select convert(datetime,replace(@s,'-',' '))

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[MsMellow]

But that does not make a DATE data type.

 

[SQLDenis]

SQL Server has 2 date types (as of sql server 2005)
datetime and smalldatetime
the code I gave you DOES make it a datetime

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[MsMellow]

Thank you.

I think that the problem might be that even through we are using SQL SERVER 2005, our instance is still SQL SERVER 2000.

I get error:

'The conversion of a char data type to a datetime data type resulted in an out-of-range datatime value' for the code you gave and all else that I have tried.

MsMellow

 

[gmmastros]

>> I think that the problem might be that even through we are using SQL SERVER 2005, our instance is still SQL SERVER 2000.

No. This particular behavior has not changed from 2000 to 2005. I suspect that you have some bad data.

To find your bad data...

Select *
From   [!]TableName[/!]
Where  IsDate(replace([!]DateColumn[/!],'-',' ')) = 0

Of course, you will need to replace DateColumn and TableName to match the names in your database.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom