CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldatedatetime
Stew
Stew

Reputation: 305

Convert varchar dd/mm/yyyy to dd/mm/yyyy datetime

I'm trying to convert a date in a varchar column in the dd/mm/yyyy format into the datetime dd/mm/yyyy format, so then I can run date range queries on the data.

So far I have the following which is not working

CONVERT(varchar, CAST(date_started AS datetime), 103)

I have also tried

convert(date_started as datetime, 103)

I need to make sure the output is as dd/mm/yyyy as we're in the UK and not the mm/dd/yyyy format

Upvotes: 9

Views: 115487

Answers (5)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

You can do like this:

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

Upvotes: 0

ElenaSQL
ElenaSQL

Reputation: 25

If you want to return a format mm/dd/yyyy, then use 101 instead of 103: CONVERT(VARCHAR(10), [MyDate], 101)

Upvotes: 0

Adrian S.
Adrian S.

Reputation: 137

I think that more accurate is this syntax:

SELECT CONVERT(CHAR(10), GETDATE(), 103)

I add SELECT and GETDATE() for instant testing purposes :)

Upvotes: 0

senthilkumar2185
senthilkumar2185

Reputation: 2566

Try this code:

CONVERT(varchar(15), date_started, 103)

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I think you are after this:

CONVERT(datetime, date_as_string, 103)

Notice, that datetime hasn't any format. You think about its presentation. To get the data of datetime in an appropriate format you can use

CONVERT(varchar, date_as_datetime, 103)

Upvotes: 21

Related Questions