Sunday, February 24, 2013

SQL BLOG: Playing with Date Time

Below code returns remaining days in the month based on the input date. We can have this inside a function also which will return the integer value.


/* code starts here */

/* calculate remaining days in the month from the given input */
declare @tdate datetime
set @tdate = '2011-02-10'
select (CAST(DATEPART(DD,@tdate) as varchar(4))+'-'+CAST(DATEPART(MM,@tdate) as varchar(4))+'-'+CAST(DATEPART(YYYY,@tdate) as varchar(4))) As Today
,case when DATEPART(MM,@tdate) in (1,3,5,7,8,10,12)
     then (31-DATEPART(DD,@tdate))
     when DATEPART(MM,@tdate) in (4,6,9,11)
     then (30-DATEPART(DD,@tdate))
     when (DATEPART(MM,@tdate) = 2) and ((DATEPART(YYYY,@tdate) % 4) = 0)
     then (29-DATEPART(DD,@tdate))
     else  (28-DATEPART(DD,@tdate))
     end as Month_Remaining_Days

Run the above code in the query editor. Sample output is:


Today                       Months_Remaining_Days
2011-02-10               18

2 comments:

  1. Above script is converted into scalar user defined function....

    /* Function for Calculating remaining days in the month from the given input */
    create function udf_calculate_remaining_days(@tdate datetime)
    returns integer
    as
    begin
    declare @iresult integer
    set @iresult =
    --(CAST(DATEPART(DD,@tdate) as varchar(4))+'-'+CAST(DATEPART(MM,@tdate) as varchar(4))+'-'+CAST(DATEPART(YYYY,@tdate) as varchar(4))) As Today
    case when DATEPART(MM,@tdate) in (1,3,5,7,8,10,12)
    then (31-DATEPART(DD,@tdate))
    when DATEPART(MM,@tdate) in (4,6,9,11)
    then (30-DATEPART(DD,@tdate))
    when (DATEPART(MM,@tdate) = 2) and ((DATEPART(YYYY,@tdate) % 4) = 0)
    then (29-DATEPART(DD,@tdate))
    else (28-DATEPART(DD,@tdate))
    end
    return @iresult
    end
    select dbo.udf_calculate_remaining_days('2012-02-10')

    ReplyDelete
  2. Awesome post
    This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of trackbacks??
    http://www.sqlservermasters.com/

    ReplyDelete