CODEWITHSUNDEEP
sql-serverdateaddgetdate
FatBoySlim7
FatBoySlim7

Reputation: 232

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))

Upvotes: 1

Views: 1465

Answers (2)

miroxlav
miroxlav

Reputation: 12194

This will leave you with DateTime value taken from tblDateApt.Date decreased by one year and increased by 6 months (as per your intent):

SELECT DATEADD(month,
               6,
               DATEADD(year, 
                       YEAR(GETDATE()) - YEAR(tblDateApt.date) - 1, 
                       tblDateApt.date
                      )
              )

Avoid any conversions to and from text content.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82484

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.

Upvotes: 2

Related Questions