开发者

converting to yyyy/dd/mm

I have a table called Raw_Data and the data looks like:

ID    Res_Date
1     2010-08-09 00:00:00.000
2     2010-07-09 00:00:00.000
3     2010-02-09 00:00:00.000

I am looking for output to be :

ID   Res_Date
1    2010-09-08 00:00:00.000
2    2010-09-07 00:00:00.000
3    2010-09-02 00:00:00.000

I tried following query;

Select ID,convert(datetime,Res_date,121) from Raw_data

but the format开发者_StackOverflow中文版 doesn't change. Can anyone help me.


You can try this if you want dashes in your results

SELECT LEFT(convert(varchar, Res_date, 102), 4) + '-' 
    + LEFT(convert(varchar, Res_date, 105), 5) + ' '
    + RIGHT(convert(varchar, Res_date, 121), 12)
from Raw_data

If you want slashes:

SELECT LEFT(convert(varchar, Res_date, 102), 4) + '/' 
    + LEFT(convert(varchar, Res_date, 103), 5) + ' '
    + RIGHT(convert(varchar, Res_date, 121), 12)
from Raw_data

Here are some resources on formatting datetime

http://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/


Try casting to a VARCHAR (or similar)?

A date has it's own native "tostring" for the purpose of queries/output. If you want the change to show up, it needs to be output in a string format.


SELECT CONVERT(NVARCHAR(10), Res_date, 121)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜