Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Converting to date format MMM-DD-YYYY

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 example

SELECT 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
Go to Top of Page

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
Go to Top of Page

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-12-13 : 12:11:53
Any ideas? I needed this soon. Thanks in advance.
Go to Top of Page

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-YYYY


select STUFF(SUBSTRING(CONVERT(varchar(25), GetDate(), 113),4 ,8),4, 1, '-')


Dec-2010
Go to Top of Page

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.DT
from
( -- 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.000
Dec-12-2010 2010-12-12 00:00:00.000
Dec-31-2010 2010-12-31 00:00:00.000[/code]


CODO ERGO SUM
Go to Top of Page

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-2010

select 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


Go to Top of Page

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!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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
Go to Top of Page

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), ' ', '-')

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-22 : 04:32:32
Do the formation in EXCEL. EXCEL wont consider VARCHARs as dates

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -