HKFuey
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?
I need to convert it to MM-YYYY so it sorts a-z in Excel.
Anyone know the syntax?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
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
ASKER
Thanks for the speedy help!
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)
https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
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
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
ASKER
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