Author |
Topic |
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-13 : 11:29:46
|
Hi,Using sql 2005, I have the date format to be displayed as MMM-DD-YYYY. I tried for exampleSELECT CONVERT(varchar,GetDate(),100) but this is not giving me what I want.Any ideas. Thanks |
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-13 : 11:36:53
|
I tried thi but how do I get teh hyphens?SELECT CONVERT(VARCHAR(12), GETDATE(), 100) AS [MMM-DD-YYYY]Dec 13 2010 |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-13 : 11:50:35
|
I looked up some date format conversions but could not find any. I am able to do this is crystal but not sure how in SQL. Should I usr str or datepart function? Any help is appreciated.Thanks |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-13 : 12:11:53
|
Any ideas? I needed this soon. Thanks in advance. |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-13 : 12:28:51
|
I was able to get the month and year as MMM-YYYY. Now I need the day as well like MMM-DD-YYYYselect STUFF(SUBSTRING(CONVERT(varchar(25), GetDate(), 113),4 ,8),4, 1, '-') Dec-2010 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-12-13 : 14:33:48
|
[code]select [MMM-DD-YYYY] = left(replace(replace(convert(varchar(30),a.DT,109),' ','-0'),' ','-'),11), a.DTfrom ( -- Test Data select DT =convert(datetime,'20101201') union all select DT =convert(datetime,'20101212') union all select DT =convert(datetime,'20101231') ) a[/code]Results:[code]MMM-DD-YYYY DT----------- -----------------------Dec-01-2010 2010-12-01 00:00:00.000Dec-12-2010 2010-12-12 00:00:00.000Dec-31-2010 2010-12-31 00:00:00.000[/code]CODO ERGO SUM |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-21 : 09:44:22
|
The above worked as well. But I tried these to get the format as Jan-21-2010select REPLACE(REPLACE(CONVERT(nvarchar(100), GetDate(), 107),' ','-'),',', '')But when I paste into excel, I am unable to format the cell to any date format as user might want to paste and format the cell to say Mar-01 etc.I found another convert statement but this gives the format as dd-mmm-yyyy. I was able to paste in excel and format the cell into any date format.SELECT REPLACE(CONVERT(nvarchar(30),getdate(),106), ' ', '-')Any ideas on how to get the format mmm-dd-yyyy and as well paste into excel and reformat into any date format?Your help is greatly appreciated.Thanks |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-12-21 : 10:00:43
|
To format things in Excel:-->Format-->Cells-->Custom Type:mmm-dd-yyy. But this assumes that the underlying cell is already a recognizable date format. This is why you should just pull in a date as a date and format in the front end!JimEveryday I learn something that somebody else already knew |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-21 : 10:09:02
|
But the preferred requirement is to be able to view data from the database in the MMM-DD-YYYY format, then when copying and pasting into excel, being able format the cell into any date format.For now, the users are living with getting a format of mm/dd/yyyy and then copying and pasting into excel, format teh cell as any date format.It would be a big help if I can get help figuring out this.Appreciate your response and help!Thanks |
|
|
sqlnovice123
Constraint Violating Yak Guru
262 Posts |
Posted - 2010-12-21 : 11:32:36
|
I believe mmm-dd-yyyy is not a recognized date format and hence I am unable to format into any date format when paste into excel.The closest I can get to is dd-mmm-yyyy which is recognized and can be converted into any date format in excel.SELECT REPLACE(CONVERT(nvarchar(11),getdate(),106), ' ', '-') |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-12-22 : 04:32:32
|
Do the formation in EXCEL. EXCEL wont consider VARCHARs as datesMadhivananFailing to plan is Planning to fail |
|
|
|