SQL convert date style formatting

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)

Select allOpen in new window

Replace @myDate with whatever you want to call the variable.

convert(nvarchar(10),getdate(),112) returns a date in the format YYYYMMDD (8 characters)

see: https://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.

Just to clarify I am looking for it to be "2013OCT".  I am not using it as a date but as a batch number.

Thank you

no problem, thanks for the question, cheers. Paul