bithday - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

bithday

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-11 : 07:18:04

Hi,
I want to calculate days till to birthday from today.
for example if today be '2012-02-03' and birthday be '2000-02-02'
my required value is : 364 days.

or today = '2012-04-25' and birthday = '2000-04-26'
result should be = 1 day

help please.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-11 : 07:35:18

datediff

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 07:53:33

This can probably be done smarter but here's a take on it:

DECLARE @BirthDay date = '1976-03-06'
DECLARE @Day date = '2013-09-11'

SELECT DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)+1) AS varchar)  + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))

- Lumbago
My blog-> http://thefirstsql.com

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-11 : 11:18:19

quote:
Originally posted by Lumbago

This can probably be done smarter but here's a take on it:
DECLARE @BirthDay date = '1976-03-06'
DECLARE @Day date = '2013-09-11'

SELECT DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)+1) AS varchar) + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))

- Lumbago
My blog-> http://thefirstsql.com

Thank you,
please fix the bug.
test your code with following sample data:
DECLARE @BirthDay date = '1976-09-12'
DECLARE @Day date = '2013-09-11'

the result must be 1 rather than 366

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-11 : 12:19:54

Here is a way to get what you need:

[CODE]

SELECT (CASE WHEN (MONTH(@BirthDay) > MONTH(@Day)) OR (MONTH(@BirthDay) = MONTH(@Day) AND DAY(@BirthDay) >= DAY(@Day)) THEN
DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)) AS varchar) + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))
ELSE
DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)+1) AS varchar) + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))
END) AS DaysToNextBirthday;

[/CODE]

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-11 : 12:45:08

quote:
Originally posted by MuMu88

Here is a way to get what you need:

[CODE]

SELECT (CASE WHEN (MONTH(@BirthDay) > MONTH(@Day)) OR (MONTH(@BirthDay) = MONTH(@Day) AND DAY(@BirthDay) >= DAY(@Day)) THEN
DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)) AS varchar) + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))
ELSE
DATEDIFF(day, @Day, CAST(CAST((YEAR(@Day)+1) AS varchar) + '-' + CAST(MONTH(@BirthDay) AS varchar) + '-' + CAST(DAY(@BirthDay) as varchar) as date))
END) AS DaysToNextBirthday;

[/CODE]

Thank you, now it works.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-11 : 15:29:26

Keep it simple?

DECLARE	@Sample TABLE
	(
		Today DATETIME NOT NULL,
		Birthday DATETIME NOT NULL,
		Result SMALLINT NOT NULL
	);

INSERT	@Sample
	(
		Birthday,
		Today,
		Result
	)
VALUES	('20000202', '20120203', 364),
	('20000426', '20120425', 1),
	('19760912', '20130911', 1);

-- SwePeso
SELECT	Birthday,
	Today,
	Result,
	DATEDIFF(DAY, Today, DATEADD(YEAR, DAY(Today) / DAY(Birthday) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-11 : 16:02:15

quote:

Originally posted by SwePeso

Keep it simple?

DECLARE	@Sample TABLE
	(
		Today DATETIME NOT NULL,
		Birthday DATETIME NOT NULL,
		Result SMALLINT NOT NULL
	);

INSERT	@Sample
	(
		Birthday,
		Today,
		Result
	)
VALUES	('20000202', '20120203', 364),
	('20000426', '20120425', 1),
	('19760912', '20130911', 1);

-- SwePeso
SELECT	Birthday,
	Today,
	Result,
	DATEDIFF(DAY, Today, DATEADD(YEAR, DAY(Today) / DAY(Birthday) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you,
please test your code with following sample data, and re-create the code.
I'm afraid. I typed my desired result.

DECLARE	@Sample TABLE
	(
		Today DATETIME NOT NULL,
		Birthday DATETIME NOT NULL,
		Result SMALLINT NOT NULL
	);

INSERT	@Sample
	(
		Birthday,
		Today,
		Result
	)
VALUES	('20000202', '20120203', 365 /* 366 - 1 */), --days of year = 366
        ('20000202', '20130203', 364 /* 365 - 1 */), --days of year = 365
		('20000202', '20120204', 364 /* 366 - 2 */); --days of year = 366
-- SwePeso
SELECT	Birthday,
	Today,
	Result,
	DATEDIFF(DAY, Today, DATEADD(YEAR, DAY(Today) / DAY(Birthday) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM	@Sample;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-11 : 16:20:37

I forget the SIGN function

-- SwePeso
SELECT	Birthday,
	Today,
	Result,
	DATEDIFF(DAY, Today, DATEADD(YEAR, SIGN(DATEPART(DAYOFYEAR, Today) / DATEPART(DAYOFYEAR, Birthday)) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-11 : 16:44:24

quote:
Originally posted by SwePeso

I forget the SIGN function
-- SwePeso
SELECT Birthday,
Today,
Result,
DATEDIFF(DAY, Today, DATEADD(YEAR, SIGN(DATEPART(DAYOFYEAR, Today) / DATEPART(DAYOFYEAR, Birthday)) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM @Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you,
Please try your new code with following sample data, and fix the bug.

VALUES ('20000202', '20120203', 365), --days of year = 366
('20000202', '20130203', 364), --days of year = 365
('20000202', '20120204', 364),
('20000202', '20120104', 29), -- days of month = 31
('20111231', '20121231', 0);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-12 : 03:22:50

[code]-- SwePeso
SELECT Birthday,
Today,
Result,
DATEDIFF(DAY, Today, DATEADD(YEAR, SIGN((100 * MONTH(Today) + DAY(Today) - 1) / (100 * MONTH(Birthday) + DAY(Birthday))) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM @Sample;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-12 : 04:52:56

quote:

Originally posted by SwePeso

-- SwePeso
SELECT	Birthday,
	Today,
	Result,
	DATEDIFF(DAY, Today, DATEADD(YEAR, SIGN((100 * MONTH(Today) + DAY(Today) - 1) / (100 * MONTH(Birthday) + DAY(Birthday))) + DATEDIFF(YEAR, Birthday, Today), Birthday)) AS DaysToNextBirthday
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you,
It sounds like works finely.
But 31 is not enough instead of 100.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources