Clever Geek Handbook

SQL Server DATEPART for year and month

I have a call in SQL Server 2012 that should work on Server 2005 and 2008.

FORMAT(Date, 'yyyyMM') = 201501

is what I am currently using and you need to use older versions of SQL Server DATEPART but cannot easily do it.

Any help would be much appreciated.

+3


source to share


4 answers


Earlier versions of SQL Server do not FORMAT

, so you would need something like

YEAR(Date) = 2015 AND MONTH(Date) = 1


or something like that to check two conditions

+3


source


Should work with SQL Server 2005 and 2008

DECLARE @Date DATETIME = GETDATE();

SELECT CAST(YEAR(@Date) AS VARCHAR(4)) 
       + RIGHT('00' + CAST(MONTH(@Date) AS VARCHAR(2)),2)


RESULT:  201606


If you are going to use it in the where clause you can do something like ...

WHERE YEAR(DateColumn) = 2016
 AND  MONTH(DateColumn) = 6
+1


source


If you want to use DATEPART this should work:

CAST(DATEPART(YEAR, CAST('2015-01-05' AS DATETIME)) AS VARCHAR(4))
    +RIGHT('00' + CAST(DATEPART(MM, CAST('2015-01-05' AS DATETIME)) AS VARCHAR(2)), 2)

Alternatively, you can use YEAR or MONTH as other posts suggest.

+1


source 


SELECT CONVERT(NVARCHAR(6),GETDATE(),112)
+1


source






More articles:


All Articles