Geek Answers Handbook

Convert varchar to datetime field

I'm trying to filter out some records by date, but the datetime field seems to be stored as varchar, and I'm struggling to convert it. Below is sample 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)

The following error message appears:

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

Edit - this actually displays the results in a date and time format, but with an error message, why?

What is the best way to convert my date field to a valid date and time format? Thanks

+4
source share
4 answers

Use CONVERT with style

 CONVERT(DATETIME,Datefield ,103)

To find bad data, look what it returns

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

You should always use the correct DATETIME data type to store datetime values.

+2
source

Try it. You need to add a style element to determine 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)
+2
source

see CAST and CONVERT

you need to use

 select CONVERT(datetime,your_Datefield ,103)

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

follow the link, you will get an explanation.

+1
source

You may receive an error due to the default datetime format being mm / dd / yyyy. So, if this format has your value, then it is easily converted without errors.

To do this, you need to convert the format that converts the string to the correct date and time format. For this, the cast will not work, but convert the work. Below is the link.

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

 Select convert( datetime, getdate(), 103)
+1
source

More articles:


All Articles