formating date output in sql server

Question

I have nvarchar(255) column in my table. And data is in format of 9/4/2013

SELECT 
      YEAR([Last Revision Date]) + '-'+ 
      MONTH([Last Revision Date]) + '-'+ 
      DAY([Last Revision Date])
FROM  events_tbl

Individual, they are good, but I want to put them in 1 column when doing SELECT statement

So for date:9/4/2013

gives me: 2026

And I want YYYY-MM-DD format.

Does anyone know how to do this?

would you believe me if I told you that that convert with 126 doesn't work for me? lol — Angelina, Sep 20 '13 at 19:24
it returns same format it was in to start with: 9/4/2013. I tried everything and splitting it like the above, works — Angelina, Sep 20 '13 at 19:25
@Angelina See my answer, you have to cast your column as a `DATE` or `DATETIME` before using that method. — Khan, Sep 20 '13 at 19:26

score 2 · Accepted Answer · edited Sep 20 '13 at 19:26

2

Cast your column as a DATE, then you can use the CONVERT function to convert the date to your desired format.

This should do it:

SELECT CONVERT(char(10), CAST([Last Revision Date] AS DATE),126)
FROM events_tbl

edited Sep 20 '13 at 19:26

Kermit

answered Sep 20 '13 at 19:24

Khan

ahhhh. It was a CAST... It worked...Thank you @Khan – Angelina Sep 20 '13 at 19:27

1 Answers1