Search code examples
sql-serverdateaddgetdate

Error with SQL CONVERT GETDATE() for leap years


What I'm trying to do here is add a time component to GETDATE() since it's being converted to varchar(25). This is my statement, how would I do it?

CONVERT(Varchar(25),YEAR(GETDATE())-1)

Would it be something along CONVERT(Varchar(25),year(getDate()) -1)

This CONVERT is actually a part of:

DATEADD(m, 6,CAST(CONVERT(Varchar(25),MONTH(tblDateApt. Date)) +
'/' + CONVERT(Varchar(25),DAY(tblDateApt. Date)) 
+ '/' + CONVERT(Varchar(25),YEAR(GETDATE())-1)  As DateTime))

The problem is when I run this statement on a leap year date I get an error. I'm trying to add a time to getDate before it gets casted as DATETIME

EDIT 2

I'm simply trying to make this give return a value...

select DATEADD(m, 6,CAST(CONVERT(Varchar(25),MONTH('2/29/2016')) + '/' + CONVERT(Varchar(25),DAY('2/29/2016')) + '/' + CONVERT(Varchar(25),YEAR(GETDATE())-1)   As DateTime))

Solution

  • Breaking the date into strings and rebuild it into date is almost never the correct solution.

    Assuming I understand the problem, you want to get the dates from your database, and manipulate the year part to be a year before the current year.

    Try this:

    SELECT  tblDateApt.[Date], 
            DATEADD(Month, 
                    6, 
                    DATEADD(YEAR, 
                            YEAR(GETDATE()) - 1 - YEAR(tblDateApt.[Date]),
                            tblDateApt.[Date])
            )
    FROM tblDateApt
    

    Edited to get the date of 6 months after the date in the database after manipulating it to last year.