| Author |
Topic |
|
rds207
Posting Yak Master
198 Posts |
 Posted - 2010-02-11 : 14:26:38
|
could any body please let me know how i can convert convert datetime ( yyyy-mm-dd) to date mm/dd/yyyy(mm-dd-yyyy) in sql server 2008 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 14:35:10
|
Best to do it in the front-end applicationIf that is not possible use CONVERT(varchar(10), MyDateTime, 101) which will give you "mm/dd/yyyy" - will that do? if no use REPLACE to convert "/" to"-"I am assuming that your source date (which you refer to as being in "yyyy-mm-dd" format) is actually in a DATETIME datatype object (column / variable), but if it is in a string you will have to first convert it to DATETIME with CONVERT(datetime, MyYYYYMMDDobject, 120), so:SELECT REPLACE(CONVERT(varchar(10), CONVERT(datetime, '2010-02-11', 120), 101), '/', '-')gives"02-11-2010" |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-11 : 14:45:04
|
Another way is to use convert option 110, which would give you the date in mm-dd-yyyySELECT CONVERT(varchar(10), CONVERT(datetime, '2010-02-11', 120), 110) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 14:57:56
|
"Another way is to use convert option 110, which would give you the date in mm-dd-yyyy"Ah yes, so it does  |
|
|
|
rds207
Posting Yak Master
198 Posts |
Posted - 2010-02-11 : 16:34:37
|
Sorry i reedited...My SOURCE column datatype is datetime , format is yyyy-mm-ddMy DESTINATION column datatype is date , and the format should be mm/dd/yyyyquote:
Originally posted by rds207
My destination column datatype is datetime , format is yyyy-mm-ddMy source column datatype is date , and the format should be mm/dd/yyyywhen i execute the below query( there are some date conversions included in the query)Select CONVERT(varchar(10),dateadd(d, datediff(d, 0, dateadd(HOUR,-7,EC_FINISH_TIME)), 0), 101)from DW_T_EC_SBAthis work fine with the output(mm/dd/yyyy) , but when i insert the data INTO THE TABLE , it is getting inserted as yyyy-mm-dd...could anyone please help ...do i need to change the datatype of my destination column? if yes what it has to be?i dont want time to be displayed in the destination column , thats the reason i used date..please helpquote:
Originally posted by Kristen
"Another way is to use convert option 110, which would give you the date in mm-dd-yyyy"Ah yes, so it does
|
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-11 : 17:23:53
|
| Why are you worried about format of 'date' datatype. SQL server stores it as yyyy-mm-dd. You can change the format while selecting the values based on the CONVERT types that is already mentioned. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-12 : 01:15:54
|
quote:
Originally posted by rds207
could any body please let me know how i can convert convert datetime ( yyyy-mm-dd) to date mm/dd/yyyy(mm-dd-yyyy) in sql server 2008
Where do you want to show formatted dates?MadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-12 : 01:28:03
|
quote:
Originally posted by rds207
Sorry i reedited...My SOURCE column datatype is datetime , format is yyyy-mm-ddMy DESTINATION column datatype is date , and the format should be mm/dd/yyyyquote:
Originally posted by rds207
My destination column datatype is datetime , format is yyyy-mm-ddMy source column datatype is date , and the format should be mm/dd/yyyywhen i execute the below query( there are some date conversions included in the query)Select CONVERT(varchar(10),dateadd(d, datediff(d, 0, dateadd(HOUR,-7,EC_FINISH_TIME)), 0), 101)from DW_T_EC_SBAthis work fine with the output(mm/dd/yyyy) , but when i insert the data INTO THE TABLE , it is getting inserted as yyyy-mm-dd...could anyone please help ...do i need to change the datatype of my destination column? if yes what it has to be?i dont want time to be displayed in the destination column , thats the reason i used date..please helpquote:
Originally posted by Kristen
"Another way is to use convert option 110, which would give you the date in mm-dd-yyyy"Ah yes, so it does
provided your destination is sql server and datatype used is date it will always be stored in ccyy-mm-dd format. you dont need to worry on format while storing dates as you can always use formatting functions while retrieving from table to display date in the way you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-12 : 01:36:12
|
| Actually dates are internally stored as Integers in the tableMadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-12 : 01:38:33
|
quote:
Originally posted by madhivanan
Actually dates are internally stored as Integers in the tableMadhivananFailing to plan is Planning to fail
agreed. But what I told was how we see data when we query the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-12 : 01:40:01
|
quote:
Originally posted by visakh16
quote:
Originally posted by madhivanan
Actually dates are internally stored as Integers in the tableMadhivananFailing to plan is Planning to fail
agreed. But what I told was how we see data when we query the table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Yes it is MadhivananFailing to plan is Planning to fail |
|
|
|
|
convert date ( yyyy-mm-dd) to (mm-dd-yyyy)