CODEWITHSUNDEEP
sqlsql-server
rahul16590
rahul16590

Reputation: 401

How to convert string dd.mm.yyyy to date in SQL

I have column field of varchar type in format dd.mm.yyyy. I am trying to convert this varchar field to date time in SQL server using 

CONVERT(Datetime, LTRIM(RTRIM([Completion Date])), 102)

but it gives me error 'Conversion of varchar type to date type results in out of range value'

How to convert this field to datetime format?

Upvotes: 2

Views: 13078

Answers (4)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can use convert with date and datetime as below:

SELECT convert(datetime, '27-09-2013', 104)
SELECT convert(date, '27-09-2013', 104)

Upvotes: 0

Laxmi
Laxmi

Reputation: 3800

You can also use this:

 SELECT convert(date, '21.12.2016', 104)

enter image description here

Upvotes: 0

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

Reputation: 93704

use style 104 (dd.mm.yyyy) instead of 102 (yyyy.mm.dd)

CONVERT(Datetime, LTRIM(RTRIM([Completion Date])), 104)

Also the field of type varchar so there could be some bad dates which needs to be eliminated before the conversion .

If you are using SQL SERVER 2012+ then use TRY_CONVERT, for bad dates it will result NULL

TRY_CONVERT(Datetime, LTRIM(RTRIM([Completion Date])), 104)

Upvotes: 3

eBourgess
eBourgess

Reputation: 303

You can always use this: 

 SELECT convert(datetime, '23/07/2009', 102)

Upvotes: 2

Related Questions