convert datetime from varchar 1

[Rene1024]

Hello,

I've read some of the post in this subject, but still don't understand the process.

I have a varchar field called dateofbirth that I need to convert to datetime MM.DD.YYYY

what's the syntax to accomplish this?

Please go slow I'm very new to TSQL

Thanks


Rgds.

Rene

 

[JamesLean]

Do you mean the varchar data is in that format? If so, use:

SET DATEFORMAT mdy

SELECT CONVERT(datetime, col1)
FROM tbl

The SET DATEFORMAT just tells SQL Server that your character data is in that order (month, day, year).

--James

 

[SQLBill]

Are you trying to just display the information in that format? - If so, James' suggestion should work.

Or are you trying to change the way it is stored in SQL Server?

-SQLBill

 

[Rene1024]

Thanks for your help,

At this moment I just need to display the information so that I can make select statements such as:

Select dateofbirth
from dbo.table
where dateofbirth between '01/01/1995' and '12/31/2000'


But I would ultimately like to convert the whole column to a datetime field.

What do you recommend?

Thanks.

Rgds.

Rene

 

[Rene1024]

HI,

I tried James' suggestion, but I got dates outside the range that I wanted.

I got a message that said:

(1261582) rows affected

"The conversion of a char data type to a datetime daata type resulted in an out of range datetime value"

Why is that?
This is my select statement:

SET DATEFORMAT mdy

Select convert(datetime, dateofbirth)
from dbo.table
where dateofbirth between '01/01/1960' and '12/31/2000'

What am I doing worng?

Thanks again.

 

[JamesLean]

Firstly, your select should look like:

SELECT dateofbirth FROM tbl
WHERE CONVERT(datetime, dateofbirth) BETWEEN '01/01/1960' AND '12/31/2000'

Having said that, your error is probably occuring because you have data in your column that is in a different format to what you state (mm.dd.yyyy). Maybe someone has entered it in the format dd.mm.yyyy or you have miscellaneous charecters in there somewhere. Check your data for this.

--James

 

[Rene1024]

Thanks James I'll give that a try.

What if I decide to permanently convert the field to datetime datatype,
how can I acomplish that?

I have some dates that range from '01/01/1775' to present time, I also have some nulls.

I can't thank you enough, as I mentioned before I'm very new to TSQL.

Take care,

Rene