jeudi 17 décembre 2015

Getting wrong year for the current date in SQL server

I have a Stored Procedure which gives me Last date of month

I tried like below

select @actuallastdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@actualMonth),0))

but it is giving me wrong year as, the result is

Dec 31 1899 11:59PM

The SP is as below

ALTER PROCEDURE Monthly_leave_Allocation_2016
@Year int,
@Month int
    AS
       BEGIN
        declare  @actualMonth int
        declare  @actualYear int
        declare  @actuallastdate datetime
     BEGIN
           IF (@Month = 1)
                  BEGIN
                    set  @actualYear = @Year - 1
                    set  @actualMonth = 11
                 END
            ELSE
                  BEGIN
                    set @actualMonth = @Month - 1
                    set @actualYear = @Year
                 END
            select @actuallastdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@actualMonth),0))
       END    
            print @actuallastdate END      GO

I guess it may be related to timezone issue as my System date takes the date in dd/mm/yyyy format.

So what's going wrong here. any idea

I am using SQL-server-2005

Aucun commentaire:

Enregistrer un commentaire