Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Changing Date Style

Status
Not open for further replies.

SyntaxTerror

Technical User
Jan 5, 2011
52
US
I'm trying to change a date from 2005-10-01 00:00:00.000 to Oct-05.

I would need this to work for any month. How can I do this?

I'm using SQL Server 2005.

Thanks!
 
CAST, CONVERT, and DATEPART in SQL BOL are your friends.
The fact that you want the text makes this interesting.

GMM probably has a more elegant solution, but this is what you get from me:

Code:
select convert(varchar(3), GetDate())+ '-'+right(Datepart(yy, GetDate()),2)

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD
 
Look at the CONVERT function (Cast and Convert (Tramsact-SQL).

Or you could you the DATENAME function.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Very elegant :) I ended up having to use a varchar 12 and ended up with this beautiful mess. It's ugly... but it works!

It starts as a char(6) in 200510 format and ends up as Oct-05

STUFF(CONVERT(VARCHAR(12),CONVERT(DATETIME, (SUBSTRING(MyField, 5, 2) + '-01-' + SUBSTRING(MyField, 1, 4)), 101),107),4,7,'-')
FROM MyTable

Thanks guys! :D
 
Rudy, I think you also meant 7 instead of 107.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Busy day for me. Anyway, here's what I came up with.

Code:
Select Stuff(Convert(VarChar(12), GetDate(), 107), 4,7, '-')


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Feels like a Monday. I didn't notice that bit.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top