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)
精彩评论