Hi, today I show the uses of so many Date functions in SQL Server. By use these how can we get various type of date result.
Get Today,First Date,Last Date of Current and Previous month also.
USE tempdb
GO
DECLARE @MONTH INT = 8
DECLARE @YEAR INT = 2016
SELECT DATEADD(MONTH,@MONTH-1,DATEADD(YEAR,@YEAR-1900,0))AS DATE_VALUE, 'FIRST DAY' AS DATE_TYPE
UNION ALL
SELECT CONVERT(VARCHAR(25),GETDATE(),101) AS DATE_VALUE, 'TODAY' AS DATE_TYPE
UNION ALL
SELECT DATEADD(DAY,-1,DATEADD(MONTH,@MONTH,DATEADD(YEAR,@YEAR-1900,0)))AS DATE_VALUE, 'LAST DAY' AS DATE_TYPE
GO
DECLARE @MYDATE DATETIME = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(@MYDATE)),@MYDATE),103),'LAST DAY OF PREVIOUS
MONTH' AS DATE_TYPE
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(@MYDATE)-1),@MYDATE),103) AS DATE_VALUE,'FIRST DAY OF CURRENT MONTH' AS
DATE_TYPE
UNION ALL
SELECT CONVERT(VARCHAR(25),@MYDATE,103) AS DATE_VALUE, 'TODAY' AS DATE_TYPE
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,1,@MYDATE))),DATEADD(MM,1,@MYDATE)),103),'LAST DAY OF CURRENT MONTH'
AS DATE_TYPE
UNION ALL
SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,1,@MYDATE))-1),DATEADD(MM,1,@MYDATE)),103),'FIRST DAY OF NEXT MONTH' AS
DATE_TYPE
GO
Get Week Day and Date using CTE.
DECLARE @MONTH INT = 8
DECLARE @YEAR INT = 2016
;WITH CTE_CAL ([DATE], [DAY])
AS
(SELECT CONVERT(DATE,CONVERT(VARCHAR,(((@YEAR*100)+@MONTH)*100)+1)),
DATENAME(WEEKDAY,CONVERT(DATE,CONVERT(VARCHAR,(((@YEAR*100)+@MONTH)*100)+1)))
UNION ALL
SELECT DATEADD(DD,1,[DATE]),DATENAME(WEEKDAY, DATEADD(DD,1,[DATE]))
FROM CTE_CAL
WHERE MONTH(DATEADD(DD,1,[DATE])) = @MONTH
)
SELECT [DATE], [DAY] FROM CTE_CAL
GO
Get Date Calendar using Pivot Table.
SELECT * FROM (SELECT DATEPART(WEEK,[DATE])'WEEKDAY',* FROM CTE_CAL) V1
PIVOT
(MAX([DATE]) FOR [DAY]
IN
(Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday))AS PVT
GO
Get Calendar Table.
DECLARE @DATE AS DATE = '20160820'
DECLARE @FIRSTDATE
AS DATE = CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(@DATE)-1),@DATE),101)
DECLARE @LASTDATE AS DATE = CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,1,@DATE))),DATEADD(MM,1,@DATE)),101)
DECLARE @DAY AS INT = DATEPART(DAY,@FIRSTDATE)
IF EXISTS (SELECT * FROM information_schema.tables WHERE
Table_Name = 'Calendar'
AND Table_Type =
'BASE TABLE')
BEGIN
DROP TABLE
[Calendar]
END
CREATE TABLE [Calendar]([Date]
DATETIME, [Day]
INT)
WHILE @FIRSTDATE
<= @LASTDATE
BEGIN
INSERT
INTO [Calendar] ([Date],[Day])
SELECT
@FIRSTDATE,@DAY
SET
@FIRSTDATE = DATEADD(dd, 1, @FIRSTDATE)
SET
@DAY = DATEPART(DAY,@FIRSTDATE)
END
SELECT * FROM [Calendar]
GO
Get Calendar Table using CTE.
DECLARE @DATE AS DATE = '20160820'
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE
xtype = 'U' AND ID = OBJECT_ID(N'tempdb..#TEMP1'))
BEGIN
DROP TABLE
#TEMP1
END
;WITH CTE_CAL ([Date], [Day])
AS
(
Select CONVERT(date,convert(varchar,(((DATEPART(YEAR,@DATE)*100)+DATEPART(MONTH,@DATE))*100)+1)),
DATEPART(DAY,CONVERT(date,convert(varchar,(((DATEPART(YEAR,@DATE)*100)+DATEPART(MONTH,@DATE))*100)+1)))
UNION ALL
Select DATEADD(DD,1,[Date]),
DATEPART(DAY,DATEADD(DD,1,[Date]))
FROM CTE_CAL
WHERE month(DATEADD(DD,1,[Date])) = DATEPART(MONTH,@DATE)
)
SELECT * FROM CTE_CAL
GO
Age Calculator.
DECLARE @BIRTHDATE
AS DATETIME = '1987-08-20 07:50:00.000'
DECLARE @TODATE AS DATETIME = GETDATE()
SELECT DATEPART (YEAR, @TODATE-@BIRTHDATE)-1900 AS [Years],
DATEPART (MONTH, @TODATE-@BIRTHDATE)-1 AS [Months],
DATEPART (DAY, @TODATE-@BIRTHDATE)-1 AS [Days],
DATEPART (HOUR, @TODATE-@BIRTHDATE) AS [Hours],
DATEPART (MINUTE, @TODATE-@BIRTHDATE) AS [Minutes],
DATEPART (SECOND, @TODATE-@BIRTHDATE) AS [Seconds],
DATEPART (MILLISECOND, @TODATE-@BIRTHDATE) AS [Milliseconds]
GO
If there is any other better option,
Thank You,
Arka Gupta.
