CODEWITHSUNDEEP
sql-server
HM8689
HM8689

Reputation: 143

Convert varchar to datefield in SQL Server

I am aware that this is a very common and have tried using the solutions suggested on similar questions. However, I cant seem to make it work.

I have a column which contains dates, but it is of varchar datatype.

The data looks like this:

startdate
-----------
15/01/2007
29/06/1998
07/12/2001

and so on..

I tried the following command

try_convert(datetime, startdate) as 'startdate'

But it returns a lot of NULLs even when there is a date populated in the original column.

Can someone please help?

Upvotes: 0

Views: 33

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

The try_convert method accepts three arguments: Data type, Expression, and Style. You need to use the Style argument:

try_convert(datetime, startdate, 103) as 'startdate' -- 103 is dd/MM/yyyy

(a list of supported styles can be found here)

Also, You should store date values as Date, not as varchar. For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type

Upvotes: 1

Related Questions