SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 bithday
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/11/2013 :  07:18:04  Show Profile  Reply with Quote
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

938 Posts

Posted - 09/11/2013 :  07:35:18  Show Profile  Reply with Quote
datediff
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 09/11/2013 :  07:53:33  Show Profile  Reply with Quote
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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/11/2013 :  11:18:19  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/11/2013 :  12:19:54  Show Profile  Reply with Quote
Here is a way to get what you need:



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;



Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/11/2013 :  12:45:08  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Here is a way to get what you need:



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;






Thank you, now it works.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/11/2013 :  15:29:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/11/2013 15:30:11
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/11/2013 :  16:02:15  Show Profile  Reply with Quote
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;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/11/2013 :  16:20:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/11/2013 16:27:02
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/11/2013 :  16:44:24  Show Profile  Reply with Quote
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);
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/12/2013 :  03:22:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/12/2013 :  04:52:56  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000