CODEWITHSUNDEEP
sqlsql-server
TheRealHamzaYusuf
TheRealHamzaYusuf

Reputation: 85

Converting a varchar column to datetime

I have a table that has a column for dateofbirth varchar(10). All the data inside is stored like this '02/01/1990'.

I need to convert it to datetime. I've tried 

CAST(DateofBirth AS DATEITME) AS BirthDate

But I keep getting this error when I try importing:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"

I need it like this:

1990-02-01 00:00:00:000

Upvotes: 1

Views: 8872

Answers (4)

GMB
GMB

Reputation: 222702

You need to convert a Varchar in format MM/DD/YYYY to a Datetime.

Sql server recognizes a set of predefined date formats that it is able to automatically parse. See this cheat list.

Your input format corresponds to sql server date format 101, hence you can do :

SELECT CONVERT(Datetime, '02/01/1990', 101)

Upvotes: 0

level3looper
level3looper

Reputation: 1051

Your cast statement works for me: 

Declare @dateofbirth varchar(10) = '02/01/1990'
select cast(@dateofbirth as datetime)AS BirthDate

Result:

BirthDate
1990-02-01 00:00:00.000

Upvotes: 0

pniel
pniel

Reputation: 1

If you try to do it in sql query there is syntax to do it . Here a link .. SQL Server Convert Varchar to Datetime

Upvotes: 0

stackoverflow
stackoverflow

Reputation: 102

SELECT CONVERT(Datetime, '2011-09-28 18:01:00', 120) -- to convert it to Datetime SELECT CONVERT( VARCHAR(30), @date ,105) -- italian format [28-09-2011 18:01:00] + ' ' + SELECT CONVERT( VARCHAR(30), @date ,108 ) -- full date [with time/minutes/sec]

Upvotes: 1

Related Questions