Sunday, July 3, 2016

SQL SERVER: Get FIRST, LAST date and Total days in year

In this article I am going to explain how to get first and last date of year, count total number of days in year in Sql server.


Run the below given Sql queries to get first, last date of year and count the number of days in year.

--First day of Year--
SELECT [First Day of Year]=DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

--Last day of Year--
SELECT [Last Day of Year] = DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

--Total number day of Days--
DECLARE @year int
SET @year=YEAR(getdate())
Select [Total number day of Days]= DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0),DATEADD(YEAR,@year-1900+1,0))

Run the below given query to get Start, end date of year and count number of days in year:
declare @date date, @total int
set @date= getdate()
set @total =year(@date)
   DATEADD(yy, DATEDIFF(yy,0,@date), 0) AS [First day of Year]
   ,DATEADD(yy, DATEDIFF(yy,0,@date) + 1, -1) AS [Last day of Year]
   ,DATEDIFF(DAY,DATEADD(YEAR,@total-1900,0),DATEADD(YEAR,@total-1900+1,0)) AS [Total number day of Days]

No comments:

Post a Comment