asked on

SQL Query Syntax

I have a date field e.g. 2016-01-06 00:00:00.000
I need to convert it to MM-YYYY so it sorts a-z in Excel.

Anyone know the syntax?

SOLUTION

Sam Simon Nasser

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HKFuey

ASKER

OK, I have this: -
CAST(DATEPART(YYYY, TransactionDate) AS VARCHAR(4)) + '-' + CAST(DATEPART(MM, TransactionDate) AS VARCHAR(2))
Which gives 2018-3, but I would like it to read 2018-03

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sam Simon Nasser

@HKFeuy,
check this document from Microsoft regarding Date and Time Styles
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles

HKFuey

ASKER

Thanks for the speedy help!

PortletPaul

Since SQL Server 2012 it is possible to use FORMAT()

it is a lot easier to use 'yyyy-MM' than it is to remember "121 truncated to 7 chars " (or similar)

DECLARE @myDateTime DATETIME
SET @myDateTime = '2016-01-06 00:00:00.000'
SELECT 
    FORMAT(@myDateTime,'yyyy-MM')         as by_format
  , CONVERT(VARCHAR(7), @myDateTime, 121) as by_convert

Open in new window

If you really need to use the older CONVERT() method this article may help:
https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html