Changing Date Style

[SyntaxTerror]

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!

 

[Qik3Coder]

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:

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

 

[djj55]

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!

 

[r937]

here ya go...

STUFF(CONVERT(VARCHAR(9),thedate,107),4,5,'-')



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

aaargh, i meant VARCHAR(10) of course

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SyntaxTerror]

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!

 

[gmmastros]

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

 

[r937]

not my day, is it

STUFF(CONVERT(VARCHAR(10),CONVERT(DATETIME,MyField+'01',112),7),4,5,'-')

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SyntaxTerror]

Even better! Thanks

 

[gmmastros]

Busy day for me. Anyway, here's what I came up with.

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

 

[r937]

yeah, but george...

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

my last post included the functions to convert from 200510 to a datetime


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

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