I have a Birthday Column in My Table. How Can I Figure Out When There If Someone’s Birthday is This Year?
Easy one! But every now and then such a question pops up. So, here’s one approach to solve this:
SET NOCOUNT ON
CREATE TABLE birthdays ( myName VARCHAR(20) , myBirthday DATETIME )
GO
INSERT INTO birthdays VALUES(‘Frank’, ‘19680723’)
INSERT INTO birthdays VALUES(‘Tanja’, ‘19700706’)
INSERT INTO birthdays VALUES(‘Julian’, ‘19990829’)
INSERT INTO birthdays VALUES(‘Florian’, ‘20030701’)
INSERT INTO birthdays VALUES(‘Nina’, ‘20050123’)
SET NOCOUNT OFF
DECLARE @year INT SET @year = YEAR(GETDATE()) SELECT myName , DATEADD(yyyy,@year-YEAR(myBirthday),myBirthday) AS myBirthday FROM birthdays ORDER BY myBirthday
DROP TABLE birthdays
myName myBirthday
——————– ——————————————————
Nina 2005-01-23 00:00:00.00
Florian 2005-07-01 00:00:00.000
Tanja 2005-07-06 00:00:00.000
Frank 2005-07-23 00:00:00.000
Julian 2005-08-29 00:00:00.000
(5 row(s) affected)
How does this work? The important stuff happens within the @year-YEAR(myBirthday) part. There we calculate the offset in years that needs to be added to the birthdate in order to get this years’ birthday for each person. As you also can see through the use of a variable, you have a pretty generic approach here.
I Have Three Separate Values for Day, Month and Year. How Can I turn This Into a Date?
Surely you *can* do something like this:
DECLARE @day INT, @month INT, @year INT SELECT @day = 28, @month = 2, @year = 2005 SELECT CAST( REPLACE(STR(@year,4),’ ‘,’0′) + REPLACE(STR(@month,2),’ ‘,’0′) + REPLACE(STR(@day,2),’ ‘,’0’) AS DATETIME)
——————————————————
2005-02-28 00:00:00.000
(1 row(s) affected)
Or possibly even worse, a tour de brute force with CASTings over CASTings while trying to determine whether the given day or month has one digit or two. I’m not going to show you such an example. Look at some of the questions in online communities on this topic and you’ll see what I mean.
Well, while the above solution is already a quite nice one, I know most developers are also lazy coders who tend to avoid as many keystrokes as possible. So, this one’s for you:
SELECT CAST( CAST( (@year * 10000 + @month * 100 + @day) AS CHAR(8)) AS DATETIME)
——————————————————
2005-02-28 00:00:00.000
(1 row(s) affected)
I should note one thing to keep in mind here. Both solutions do not check if you have a valid date before trying to CAST to a DATETIME. If you want to catch this, you would need to write something like:
DECLARE @day INT, @month INT, @year INT SELECT @day = 29, @month = 2, @year = 2005
SELECT CASE WHEN ISDATE(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)))=0
THEN ‘No valid date’ ELSE ‘Valid date’
END ,
CASE WHEN ISDATE(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)))=1
THEN CAST(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)) AS DATETIME)
ELSE 0
END
————- ——————————————————
No valid date 1900-01-01 00:00:00.000
(1 row(s) affected)
How Many Days Does the Month of a Given Date Have?
This simple question is actually trickier than it seems:
DECLARE @dt DATETIME SET @dt = ‘20050325’
SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt)))) AS DaysInGivenMonth
DaysInGivenMonth
—————-
31
(1 row(s) affected)
Or in a slightly shorter version:
SELECT DAY(DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1) AS DaysInGivenMonth
DaysInGivenMonth
—————-
31
(1 row(s) affected)
Well, how does this work? The “magic” is done here:
SELECT DATEADD(DAY,1-DAY(@dt),@dt)
——————————————————
2005-03-01 00:00:00.000
(1 row(s) affected)
This will always adjust the first day of the given month. The rest is straight forward. Add one month, then subtract 1 day and return that value via DAY().
On a side note. You can also use the last statement to adjust to all days less or equal to 28. That is all days that happen to be for sure in all months. If you, for example, want to adjust to the 15th of a month, you would do:
SELECT DATEADD(DAY,15-DAY(@dt),@dt)
——————————————————
2005-03-15 00:00:00.000
(1 row(s) affected)
Another method to retrieve the days of a month is:
DECLARE @dt DATETIME SET @dt = ‘20050326’
SELECT @dt ,
CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1
THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1
THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1
THEN 29 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1
THEN 28 END AS DaysInMonth
DaysInMonth
—————————————————— ———–
2005-03-26 00:00:00.000
31
(1 row(s) affected)
For better readability, you might want to consider rewriting this to:
SELECT @dt , CASE WHEN ISDATE(the_date/100*100+31) = 1 THEN 31 WHEN ISDATE(the_date/100*100+30) = 1 THEN 30 WHEN ISDATE(the_date/100*100+29) = 1 THEN 29 WHEN ISDATE(the_date/100*100+28) = 1 THEN 28 END AS DaysInMonth FROM (SELECT CONVERT(VARCHAR,@dt,112) AS the_date ) t1
but I won’t do it here, because I think the first version is a better aid in visualizing what is going on here.
Now, why does the above work? Firstly, the CONVERT to style 112 is to ensure that the result can be interpreted as a number. You then might think a value divided by 100 and again multiplied by 100 is the original value again. So why not go ahead and just do the addition? Not quite. Most of the time you would be right, but we’re doing some integer arithmetic here and so dividing by 100 and multiplying back by 100 does not result in the original value. Want to prove this?
DECLARE @dt DATETIME SET @dt = ‘20050326’
SELECT CONVERT(VARCHAR,@dt,112) AS Input ,
CONVERT(VARCHAR,@dt,112)/100*100 AS Output
Input Output
—————————— ———–
20050326 20050300 (1 row(s) affected)
Now, we can add the possible days for months and test if it yields a valid date. If so, we do have our days of the given month. Btw, don’t think you will get a “more” efficient algorithm when you reorder the WHEN expressions from most frequently occurring to least frequently. I mean, don’t put the test for days = 28 before the test for days = 29! This will yield wrong results.
DECLARE @dt DATETIME
SET @dt = ‘20040203’
SELECT @dt ,
CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1
THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1
THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1
THEN 29 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1
THEN 28 END AS DaysInMonthCorrect ,
CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1
THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1
THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1
THEN 28 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1
THEN 29 END AS DaysInMonthWrong
DaysInMonthCorrect DaysInMonthWrong
—————————————————— —————— —————-
2004-02-03 00:00:00.000 29 28
(1 row(s) affected)
Finally here, a pretty cool script for lazycoders comes from SQL Server MVP Steve Kass
DECLARE @dt DATETIME SET @dt = ‘20050326’ — or the date in question SELECT 32-DAY(@dt-DAY(@dt)+32) ———– 31 (1 row(s) affected)
Did I mention it already, that I really, really love these cool (and fast to type) scripts?