CODEWITHSUNDEEP
sqlt-sql
Waller
Waller

Reputation: 1873

T-SQL: How to convert string representations of a date

I've got some dates stored in varchar(10) format.

Dates are like so:

2008-01-06
2008-01-13
2008-01-20
2008-01-27
2008-02-03
2008-02-10
2008-02-17
2008-02-24

I would like them in dd/mm/yyyy format (also a DATE datatype)

I have tried the following:

SELECT CONVERT(VARCHAR(max), startWeek, 103) date
FROM dbo.GoogledataFinal

Why does this output:

2008-01-06
2008-01-13
2008-01-20
2008-01-27
2008-02-03
2008-02-10
2008-02-17

Would be great if you could fix my code, but I'm more interested in WHY this behaviour is happening, so explaining like I'm 5 years old might be helpful.

Upvotes: 0

Views: 363

Answers (4)

Daniel Reis
Daniel Reis

Reputation: 13372

You need to convert the varchar to a datetime, and then back to varchar using the desired format:

SELECT CONVERT( varchar(10), 
         CONVERT(DATETIME, '2008-02-03', 120) -- Varchar "yyyy-mm-dd" to Datetime
       , 103) -- Datetime to Varchar "dd/mm/yyyy"

For your case the SQL might be:

SELECT CONVERT( varchar(10), 
         CONVERT(DATETIME, startWeek, 120)
       , 103)
FROM dbo.GoogledataFinal

Upvotes: 0

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Try this:

SELECT (SUBSTRING (startWeek,9, 2) + '/' + SUBSTRING (startWeek,6, 2) + '/' SUBSTRING (startWeek,1, 4))  AS date
FROM dbo.GoogledataFinal

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You are converting from varchar to varchar. The style parameter is not even used for that.

Instead, convert to date, and then back to varchar:

select convert(varchar(10), cast('2008-01-06' as datetime), 103)

This prints:

06/01/2008

Upvotes: 5

Dennis Traub
Dennis Traub

Reputation: 51684

SELECT CONVERT(varchar(10), CONVERT(datetime, startWeek), 103) date
FROM dbo.GoogledataFinal

Upvotes: 2

Related Questions