SQL convert date style formatting

I want to get a string in the format of "YYYYMMM" from date().  So far I have it down to Convert(nvarchar(10),date(),112) as [YYYYMMM].  I don't write much sql and need to add it to a stored procedure and don't want to break it.  Thanks for any help.
gseales12Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
that would be:

select datename(year, getdate() ) + upper( left( datename(month, getdate() ) ,3) )
0
 
Lee SavidgeCommented:
Close enough. Are you just trying to get the current date in that format? If so use getdate()

declare @myDate nvarchar(8)

select @myDate = convert(nvarchar(8),getdate(),112)

Open in new window


Replace @myDate with whatever you want to call the variable.
0
 
PortletPaulfreelancerCommented:
convert(nvarchar(10),getdate(),112) returns a date in the format YYYYMMDD (8 characters)

see: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html

------

but you are asking for YYYYMMM (7 characters)
not sure if this is exactly what you meant, but YYYYMMM would be 'unusual'

e.g. 18th October 2013 would become: 2013OCT

for this I would try:

select datename(year, getdate() ) + upper( left( datename(month, getdate() ) ,3) )

or if a field, not getdate()

select datename(year, [date_field] ) + upper( left( datename(month, [date_field] ) ,3) )

return type from datename() is nvarchar
see: http://msdn.microsoft.com/en-us/library/ms174395.aspx
table of examples: http://www.dailyfreecode.com/Code/datename-function-270.aspx

-------
note also if using SQL Server 2012 there is the format() function which makes life easier with dates.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gseales12Author Commented:
Just to clarify I am looking for it to be "2013OCT".  I am not using it as a date but as a batch number.
0
 
gseales12Author Commented:
Thank you
0
 
PortletPaulfreelancerCommented:
no problem, thanks for the question, cheers. Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.