SQL SERVER – Trivia – Days in a Year

Every time I wrote a blog, I tend to get back to most of them via the search. Most of you ask me questions and I do few simple search back to make sure I am able to address them. Yesterday as I was searching for an interesting question, Now back to the question that got me there, one of a friend said he was asked in an interview on how to efficiently find the number of days in a given year.

The question was interesting but what baffled me was – do people still ask such questions? It was a challenge that I wanted to share with you. I wanted to take the challenge and immediately got to my SQL Server machine to solve. The instinctive behavior was to use some basic methods that involved DATEADD and DATEPART options.

The first solution that I got was:

Solarwinds

--This method will work on SQL server 2005 and above.
DECLARE @year AS INT
SET
@year=2012
SELECT DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))
AS [TOTAL NO OF DAYS] GO

Immediately I sent the above option. Then he quizzed me asking, can you use some of the New Date time functions that were introduced with SQL Server 2012. Now, I was not prepared for the googly that came my way. But after getting my heads around it, I came up with the below query.

--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET
@year=2012
SELECT DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [TOTAL NO OF DAYS]

Woot !!! That was not simple as I had to search my blog for ideas.

Quiz: Can you come up with some solution which will have lesser number of functions involved? Can you use some of the new SQL Server 2012 Date functions in your solution? Let me know via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Add Node in Cluster – Rule “Node and cluster edition match” failed
Next Post
Interview Question of the Week #012 – Steps to Restore Bak File to Database

Related Posts

16 Comments. Leave new

  • Michael Collins
    March 21, 2015 1:16 pm

    I am just beginning to learn programming with Transact_SQL so needed to learn how to create a function before submitting my answer with calls no system functions at all.

    This is my answer

    CREATE FUNCTION [dbo].[udf_IsLeapYear] ( @pccyy_year INT )
    RETURNS INT
    AS
    BEGIN
    DECLARE @rtLeapYear INT
    SET @rtLeapYear = CASE WHEN (@pccyy_year % 4) = 0
    AND
    ((@pccyy_year % 100) != 0)
    OR
    ((@pccyy_year % 400) = 0)
    THEN 0
    ELSE 1 END
    RETURN @rtLeapYear
    END
    GO

    SELECT dbo.udf_IsLeapYear(2012) IsLeapYear
    GO

    If you recognise the code it is almost the code in your example

    SEPTEMBER 8, 2007 BY PINAL DAVE
    SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

    I think on rereading the question you meant us to use a function/s from SQL Server 2012 so my answer is probably not what you wanted.

    More important than anything a million thanks for producing this blog, the answers you give are clear and concise.

    Again thank you

    Reply
  • Michael Collins
    March 21, 2015 1:35 pm

    I apologise to all I somehow got leap year into my head

    Reply
  • Here is another method of doing this

    Reply
  • Here is another way that works, still 2 functions though….

    declare @year_to_test char(4) = 2012
    select 368 – (datepart(month, dateadd(day, 1, @year_to_test + ‘-02-28’)))

    Reply
  • ScottPletcher
    March 23, 2015 8:00 pm

    You don’t really need any functions per se. This should work for at least thru 2099. You could further adjust it to work for any (reasonable) year.

    DECLARE @year AS INT
    SET @year=2012

    SELECT 365 + CASE WHEN @year % 4 = 0 THEN 1 ELSE 0 END

    Reply
    • Not entirely correct, if you look at some of the linked leap year posts you will see there are exceptions to the %4

      Reply
    • ScottPletcher – Your leap year calculation logic is incorrect. There are few more cases where year is divisible by 4 and still not a leap year.

      Reply
    • You just can not find modulus using 4. Refer this post for additional conditions

      Reply
  • This is Error when I am running SQL Command on destination Server

    OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “Login timeout expired”.
    OLE DB provider “SQLNCLI10” for linked server “(null)” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
    Msg 2, Level 16, State 1, Line 0
    Named Pipes Provider: Could not open a connection to SQL Server [2].

    The above error i am getting while executing storedprocedure against msdb database..please give suggestion..especcizlly in sql server 2012…same working fine in 2005 and 2008

    Reply
  • Hi pinal Thanks for your replay..i am getting this issue while execting stored procedure aginst msdb database in sql server 2012..same i executed in sql server 2008..it’s working fine..the flle acript as follows..

    USE [msdb]
    GO

    /****** Object: StoredProcedure [dbo].[uspEmailSQLServerHealth] Script Date: 03/30/2015 17:18:14 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[uspEmailSQLServerHealth]
    (
    @ServerIP VARCHAR(100), — SQL Server 2005 Database Server IP Address
    @Project VARCHAR(100), — Name of project or cleint
    @Recepients VARCHAR(2000), — Recepient(s) of this email (; separated in case of multiple recepients).
    @MailProfile VARCHAR(100), — Mail profile name which exists on the target database server
    @Owner VARCHAR(200) — Owner, basically name/email of the DBA responsible for the server
    )

    /*

    exec EmailSQLServerHealth ‘10.10.10.10’, ‘MYProject’, ‘myself@mycompany.com’, ‘TestMailProfile’, ‘My Self’

    */

    AS
    BEGIN

    SET NOCOUNT ON

    /* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released,
    however, good to follow this practice). */
    IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘#jobs_status’)
    BEGIN
    DROP TABLE #jobs_status
    END

    IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘#diskspace’)
    BEGIN
    DROP TABLE #diskspace
    END

    IF EXISTS (SELECT NAME FROM sysobjects WHERE name = ‘#url’)
    BEGIN
    DROP TABLE #url
    END

    IF EXISTS (SELECT NAME FROM sysobjects WHERE name = ‘#dirpaths’)
    BEGIN
    DROP TABLE #dirpaths
    END

    — Create the temp tables which will be used to hold the data.
    CREATE TABLE #url
    (
    idd INT IDENTITY (1,1),
    url VARCHAR(1000)
    )

    CREATE TABLE #dirpaths
    (
    files VARCHAR(2000)
    )

    CREATE TABLE #diskspace
    (
    drive VARCHAR(200),
    diskspace INT
    )

    — This table will hold data from sp_help_job (System sp in MSDB database)
    ————–CREATE TABLE #jobs_status
    ————–(
    ————– job_id UNIQUEIDENTIFIER,
    ————– originating_server NVARCHAR(30),
    ————– name SYSNAME,
    ————– enabled TINYINT,
    ————– description NVARCHAR(512),
    ————– start_step_id INT,
    ————– category SYSNAME,
    ————– owner SYSNAME,
    ————– notify_level_eventlog INT,
    ————– notify_level_email INT,
    ————– notify_level_netsend INT,
    ————– notify_level_page INT,
    ————– notify_email_operator SYSNAME,
    ————– notify_netsend_operator SYSNAME,
    ————– notify_page_operator SYSNAME,
    ————– delete_level INT,
    ————– date_created DATETIME,
    ————– date_modified DATETIME,
    ————– version_number INT,
    ————– last_run_date INT,
    ————– last_run_time INT,
    ————– last_run_outcome INT,
    ————– next_run_date INT,
    ————– next_run_time INT,
    ————– next_run_schedule_id INT,
    ————– current_execution_status INT,
    ————– current_execution_step SYSNAME,
    ————– current_retry_attempt INT,
    ————– has_step INT,
    ————– has_schedule INT,
    ————– has_target INT,
    ————– type INT
    ————–)

    — To insert data in couple of temp tables created above.
    INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives
    —-SET @SQL = insert into #jobs_status
    —- EXEC(@SQL)
    —-exec sp_serveroption ‘BDOLOWISTRAIN3’, ‘data access’, ‘true’
    —-select * INTO #jobs_status FROM OPENQUERY( BDOLOWISTRAIN3, ‘ EXEC msdb.dbo.sp_help_job ‘)

    —-INSERT #jobs_status EXEC master.dbo.xp_sqlagent_enum_jobs
    SELECT * INTO #jobs_status
    FROM OPENROWSET(‘sqloledb’, ‘server=(local);trusted_connection=yes’
    , ‘set fmtonly off exec msdb.dbo.sp_help_job’)

    — Variable declaration
    DECLARE @TableHTML VARCHAR(MAX),
    @StrSubject VARCHAR(100),
    @Oriserver VARCHAR(100),
    @Version VARCHAR(250),
    @Edition VARCHAR(100),
    @ISClustered VARCHAR(100),
    @SP VARCHAR(100),
    @ServerCollation VARCHAR(100),
    @SingleUser VARCHAR(5),
    @LicenseType VARCHAR(100),
    @StartDate DATETIME,
    @EndDate DATETIME,
    @Cnt int,
    @URL varchar(1000),
    @Str varchar(1000)

    — Variable Assignment
    SELECT @Version = @@version
    SELECT @Edition = CONVERT(VARCHAR(100), serverproperty(‘Edition’))
    SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + ‘-01’ AS DATETIME)
    SELECT @StartDate = @StartDate – 1
    SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + ‘-‘ + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + ‘-‘ + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)
    SET @Cnt = 0

    IF serverproperty(‘IsClustered’) = 0
    BEGIN
    SELECT @ISClustered = ‘No’
    END
    ELSE
    BEGIN
    SELECT @ISClustered = ‘YES’
    END

    SELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY (‘productlevel’))
    SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY (‘Collation’))
    SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY (‘LicenseType’))
    SELECT @SingleUser = CASE SERVERPROPERTY (‘IsSingleUser’)
    WHEN 1 THEN ‘Yes’
    WHEN 0 THEN ‘No’
    ELSE
    ‘null’ END
    SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY(‘servername’))
    SELECT @strSubject = ‘Database Server Health Checks (‘+ CONVERT(VARCHAR(50), SERVERPROPERTY(‘servername’)) + ‘)’

    /*
    Along with refrences to SQL Server System objects, You will also see lots of HTML code however do not worry,
    */
    SET @TableHTML =
    ‘Server Info


    Server IP


    Server Name


    Project/Client

    ‘+@ServerIP+’
    ‘ + @OriServer +’
    ‘+@Project+’


    Version


    Edition


    Service Pack


    Collation


    LicenseType


    SingleUser


    Clustered

    ‘+@version +’
    ‘+@edition+’
    ‘+@SP+’
    ‘+@ServerCollation+’
    ‘+@LicenseType+’
    ‘+@SingleUser+’
    ‘+@isclustered+’

     ‘ +
    ‘Job Status

    Job Name

    Enabled

    Last Run

    Category

    Last Run Date

    Execution Time (Mi)

    SELECT
    @TableHTML = @TableHTML + ” +
    ISNULL(CONVERT(VARCHAR(100), A.name), ”) +” +
    CASE enabled
    WHEN 0 THEN ‘False
    WHEN 1 THEN ‘True’
    ELSE ‘Unknown’
    END +
    CASE last_run_outcome
    WHEN 0 THEN ‘
    <a href="mailto:nauitsc@chevron.com?subject=Job failure – ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=sjvdbas@chevron.com&body = SD please log this call to DB support,' + '%0A %0A' + '<> Job Failed on ‘ + @OriServer + ‘(‘ + @ServerIP + ‘)’+ ‘.’ +’%0A%0A Regards,’+'”>Failed

    WHEN 1 THEN ‘Success’
    WHEN 3 THEN ‘Cancelled’
    WHEN 5 THEN ‘Unknown’
    ELSE ‘Other’
    END +
    ” + ISNULL(CONVERT(VARCHAR(100), A.category),”) + ” +
    ” + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),”) + ” +
    ” + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),”) +’ ‘
    FROM
    #jobs_status A
    inner join (
    select
    A.job_id,
    datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes
    from
    msdb..sysjobactivity A
    inner join (
    select
    max(session_id) sessionid,
    job_id
    from
    msdb..sysjobactivity
    group by
    job_id
    ) B on a.job_id = B.job_id and a.session_id = b.sessionid
    inner join (
    select
    distinct name,
    job_id
    from
    msdb..sysjobs
    ) C on A.job_id = c.job_id
    ) X on A.job_id = X.job_id
    ORDER BY
    last_run_date DESC
    SELECT
    @TableHTML = @TableHTML + ”
    ——–SELECT
    ——– @TableHTML = @TableHTML + ‘
    ——–   
    ——–   
    ——–   
    ——–   
    ——–   
    ——– ‘

    SELECT
    @TableHTML = @TableHTML +
    ‘Databases


    Name


    CreatedDate


    DB Size(GB)


    State


    Recovery Model

     

    select
    @TableHTML = @TableHTML +
    ” + ISNULL(name, ”) +” +
    ” + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + ‘-‘ + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + ‘-‘ + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +” +
    ” + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), ”) +” +
    ” + ISNULL(state_desc, ”) +” +
    ” + ISNULL(recovery_model_desc, ”) +”
    from
    sys.databases MST
    inner join (select b.name [LOG_DBNAME],
    CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(15,2),(a.size * 8)) /1024)/1024) [Total Size GB]
    from sys.sysaltfiles A
    inner join sys.databases B on A.dbid = B.database_id
    group by b.name)AA on AA.[LOG_DBNAME] = MST.name
    order by
    MST.name

    SELECT
    @TableHTML = @TableHTML + ”

    SELECT
    @TableHTML = @TableHTML +
    ‘Disk Stats


    Disk


    Free Space (GB)

     

    SELECT
    @TableHTML = @TableHTML +
    ” + ISNULL(CONVERT(VARCHAR(100), drive), ”) +” +
    ” + ISNULL(CONVERT(VARCHAR(100), ISNULL(CAST(CAST(diskspace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),”) +”
    FROM
    #diskspace

    SELECT @TableHTML = @TableHTML + ”

    — Code for SQL Server Database Backup Stats
    SELECT
    @TableHTML = @TableHTML +
    ‘SQL SERVER Database Backup Stats

    Date

    Database

    File Name

    Type

    Start Time

    End Time

    Size(GB)

     

    SELECT
    @TableHTML = @TableHTML +

    ‘ + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + ‘-‘ + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + ‘-‘ + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),”) +” +
    ” + ISNULL(CONVERT(VARCHAR(100), MST.database_name), ”) +” +
    ” + ISNULL(CONVERT(VARCHAR(100), MST.name), ”) +” +
    CASE Type
    WHEN ‘D’ THEN ” + ‘Full’ +”
    WHEN ‘I’ THEN ” + ‘Differential’ +”
    WHEN ‘L’ THEN ” + ‘Log’ +”
    WHEN ‘F’ THEN ” + ‘File or Filegroup’ +”
    WHEN ‘G’ THEN ” + ‘File Differential’ +”
    WHEN ‘P’ THEN ” + ‘Partial’ +”
    WHEN ‘Q’ THEN ” + ‘Partial Differential’ +”
    ELSE ” + ‘Unknown’ +”
    END +
    ” + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), ”) +” +
    ” + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), ”) +” +
    ” + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), ”) +” +

    FROM
    backupset MST
    WHERE
    MST.backup_start_date BETWEEN @StartDate AND @EndDate
    ORDER BY
    MST.backup_start_date DESC

    SELECT @TableHTML = @TableHTML + ”

    — Code for physical database backup file present on disk
    INSERT #url
    SELECT DISTINCT
    SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) – CHARINDEX(‘\’, REVERSE(BMF.physical_device_name), 0))
    from
    backupset MST
    inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
    where
    MST.backup_start_date BETWEEN @startdate AND @enddate

    select @Cnt = COUNT(*) FROM #url

    WHILE @Cnt >0
    BEGIN

    SELECT @URL = url FROM #url WHERE idd = @Cnt
    SELECT @Str = ‘EXEC master.dbo.xp_cmdshell ”dir “‘ + @URL +'” /B/O:D”’

    INSERT #dirpaths SELECT ‘PATH: ‘ + @URL
    INSERT #dirpaths

    EXEC (@Str)

    INSERT #dirpaths values(”)

    SET @Cnt = @Cnt – 1

    end

    DELETE FROM #dirpaths WHERE files IS NULL

    select
    @TableHTML = @TableHTML +
    ‘Physical Backup Files

    Physical Files

     

    SELECT
    @TableHTML = @TableHTML + ” +
    CASE SUBSTRING(files, 1, 5)
    WHEN ‘PATH:’ THEN ‘‘ + files + ‘
    ELSE
    ” + files + ”
    END +

    FROM
    #dirpaths

    SELECT
    @TableHTML = @TableHTML + ” +
    ‘ 

    Server Owner: ‘+@owner+’
    Thanks
    and Regards,
    DB
    Support Team
     ‘

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @MailProfile,
    @recipients=@Recepients,
    @subject = @strSubject,
    @body = @TableHTML,
    @body_format = ‘HTML’ ;

    SET NOCOUNT OFF
    END
    GO

    Reply
  • Shashi Pal Saini
    October 25, 2017 7:52 pm

    Calculate Days in given year

    Declare @Year INT=2016
    select DateDiff(Day, ’01-01-‘ +Convert(Varchar,@Year), ’12-31-‘ + CONVERT(Varchar, @Year) )+1

    Reply
  • DECLARE @year AS VARCHAR(16)
    SET @year=2012
    SET @year=’02/29/’+@year
    SELECT IIF(ISDATE(@year)=1,366,365)

    Reply
  • DECLARE @year AS VARCHAR(16)
    SET @year=2012
    SET @year=’02/29/’+@year
    SELECT IIF(ISDATE(@year)=1,366,365) as [days]

    Reply

Leave a Reply

Menu