Converting to date format in SQL 2005

[junction123]

I need help in getting a date format as MMM-DDD-YYYY.

I tried Option 1 below but this gives me the format as DD-MMM-YYYY. I am able to copy and paste into excel and reformat into any date format.


Option 1
SELECT REPLACE(CONVERT(nvarchar(30),getdate(),106), ' ', '-')


I tried Option 2 below an dthis gives me the correct format, however, I am unable to reformat into any date format after copying and pasting into excel.

Option 2
select REPLACE(REPLACE(CONVERT(nvarchar(100), GetDate(), 107),' ','-'),',', '')

 

[SQLBill]

Do you mean MM-DD-YYYY (there is no three digit month or day)? If so, then use:

CONVERT(VARCHAR(10), GETDATE(), 110)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[junction123]

I mean like Jan-21-2010.

MMM-DD-YYYY.

Thanks again for your help.

 

[SQLBill]

In that case, you want MON-DD-YYYY. Try:

select REPLACE(convert(varchar(11), getdate(), 100), ' ', '-')

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[junction123]

But when I copy and paste the result into excel, I am unable to format the cell to any date format.

 

[SQLBill]

That is an Excel issue then. With that code, SQL Server returns the format you asked for.

But I have to ask the question, why are you trying to change the date format in Excel? Do you or don't you want it in mon-dd-yyyy format?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sparkbyte]

That is because Excel is expecting a date format (i.e. 01/01/2010) then in the cell properties you tell excel how you want it displayed.

right click the cell/row/column and select format cells, then select date as the number type and then select how you wish excel to display the date to the user.

Or try this as a CUSTOM
[$-409]dddd, dd/dd/yyyy

this would display as Friday, 01/01/2010

Hope this helps.

Thanks

John Fuhrman

 

[junction123]

Yes, when displayed in dataview, from the database, the format should show up as mmm-dd-yyyy.

But users should have teh ability to format the cell into any date format when they copy and paste into excel.

 

[SQLBill]

John has the right idea. I did some research and MON-DD-YYYY is not a recognized date format in Excel so it can't convert it to any other date format. Even the custom dates won't work since MON-DD-YYYY isn't recognized as a date.

Run this, copy it into Excel, and convert it to see what I mean:

select convert(varchar(11), getdate(), 105)

That will work since it is a recognized Excel date format.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[junction123]

Got it. So users will have to live with either mm/dd/yyyy when viewed in data view , resultset got from teh database.

I also tried the below and I was able to convert to date when pasted into excel.

The only difference between the below result and the user preferred result is dd-mmm-yyyy instead of mmm-dd-yyyy.


SELECT REPLACE(CONVERT(nvarchar(11),getdate(),106), ' ', '-')

 

[SQLBill]

And dd-mon-yyyy is a recognized Excel date format. To see the date formats that Excel recognizes do this:

Click on a column in Excel
left click and choose Format Cells
Select the Date category.
The values in the Type window are the recognized date formats.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875