CODEWITHSUNDEEP
sqlsql-serverdatetime
GullitsMullet
GullitsMullet

Reputation: 358

convert varchar to datetime field

I'm trying to filter some records by date but the datetime field seems to be stored as varchar and I'm struggling to convert it. Below is a sample of the data:

ID      DateField
0002    14/04/1989 01:30
0003    16/04/1989 09:45
0004    16/04/1989 06:00
0005    19/04/1989 01:07
0006    21/04/1989 16:03

When I use

cast(Datefield as datetime)

I get the following error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Edit - this does actually display results in datetime format but with the error message, why is that?

What's the best way to convert my datefield to valid datetime format? Thanks

Upvotes: 4

Views: 350

Answers (4)

Madhivanan
Madhivanan

Reputation: 13700

Use CONVERT with Style

CONVERT(DATETIME,Datefield ,103)

To find bad data, see what this returns

set dateformat dmy
select Datefield from table where isdate(where)=0

You should always use proper DATETIME datatype to store datetime values

Upvotes: 2

Ajay2707
Ajay2707

Reputation: 5808

You can get the error because of by default datetime format is mm/dd/yyyy. So If your value have this format, then it easily convert without error.

For this you have to convert with format which convert the string into proper date-time format. For this cast will not work , but convert work. Here is link for more details.

As your format is dd/mm/yyyy, you need to use 103 format. Just check this to understand.

Select convert( datetime, getdate(), 103)

Upvotes: 1

A_Sk
A_Sk

Reputation: 4630

see CAST and CONVERT

you need to use 

select CONVERT(datetime,your_Datefield ,103)

because your your_Datefield holding the datetime value in dd/mm/yy format.

follow the link, you'll get the explanation.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Try this. You need to add style part to identify the format

SELECT CONVERT(DATETIME, dates, 103)
FROM   (VALUES ('14/04/1989 01:30'),
               ('16/04/1989 09:45'),
               ('16/04/1989 06:00'),
               ('19/04/1989 01:07'),
               ('21/04/1989 16:03')) cs (dates)

Upvotes: 2

Related Questions