Compare Dates and return a "Y"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare Dates and return a "Y"

Author

Topic

palbar
Starting Member

8 Posts

Posted - 2011-06-16 : 10:43:24

I have a date and I want to get the year only from that date. I then want to add a 1 to that year (variable name = new_year). I then want to concatenate the new_year to "01-FEB-" (variable name = cut_off_date). I then want to compare my original date to cut_off_date. If original date >= cut_off_date then return a "Y".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 10:57:43

I may be interpreting your description incorrectly, but if the cut-off date is always in the following year, wouldn't it always be greater than the original date? Regardless, you can find the 1st of February in the following year using this:


DATEADD(dd,31,DATEADD(yy,YEAR(OriginalDate)-1899,0))

And so your query (which would always return 'N') would be:


SELECT 
   CASE WHEN 
      OriginalDate >=  DATEADD(dd,31,DATEADD(yy,YEAR(OriginalDate)-1899,0)) THEN 'Y'
      ELSE 'N'
   END
FROM YourTable

palbar
Starting Member

8 Posts

Posted - 2011-06-16 : 12:44:50

Actually, if I run this query every day, eventually the original date will fall within the same year as the cutoff date.

palbar
Starting Member

8 Posts

Posted - 2011-06-16 : 13:25:52

quote:
Originally posted by palbar

Actually, if I run this query every day, eventually the original date will fall within the same year as the cutoff date.

I guess what I was trying to achieve was not to have to hard code 01-FEB-12 but looking at a rule I was trying to setup I'm going have to create a new rule each year and thus hard code the year in 01-FEB-year.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 15:17:52

I am afraid I am not quite following what you are trying to accomplish. You can get the first of any year using this - I am using 2008 as an example:


SELECT DATEADD(yy,2008-1900,0);

To get the first of the current year use this:


SELECT DATEADD(yy,YEAR(GETDATE())-1900,0)

Once you have the first of the year, to get Feb 2nd of that year, just add 31 days - as in:

SELECT DATEADD(dd, 31, DATEADD(yy,YEAR(GETDATE())-1900,0));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources