Calculate number of days - 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 2008 Forums

Transact-SQL (2008)

Calculate number of days

Author

Topic

sqlneofito
Starting Member

16 Posts

Posted - 2010-04-01 : 16:05:07

I have a scalar function that need to return a number of days between lets say today 2010-04-01 and christmas I got it running but I get crazy answers HELP please ... thank you!

create function dbo.NumberOfDays(@YourDate datetime) returns int
as
begin
declare @DaysLeft int

set @DaysLeft = datediff (day, @YourDate, 2010-12-25)
--set @DaysLeft = ( @YourDate - 2010-12-25)

Return @DaysLeft
end
select 2010-04-01 as 'Your Date', dbo.NumberOfDays(2010-04-01) as 'Number of Days Left'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 16:12:42

shouldnt it be?


create function dbo.NumberOfDays(@YourDate datetime) returns int
as
begin
declare @DaysLeft int 

set @DaysLeft = datediff (day, 2010-12-25, @YourDate)
--set @DaysLeft = ( @YourDate - 2010-12-25)

Return @DaysLeft
end

as per your logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlneofito
Starting Member

16 Posts

Posted - 2010-04-01 : 16:17:10

I tried that also, but gives me back 32 the number of days it should be more than 250 I think ... thank you for suggesting

sqlneofito
Starting Member

16 Posts

Posted - 2010-04-01 : 17:53:45

Any help on this please!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-01 : 18:02:08

If you are using the function above, make sure you but single quotes around the constatn date: '2010-12-25'

But, if you didn't do that you'd be getting a much larger number than 32.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-01 : 18:03:39

I see the issue.. you need to wrap the dates in single quotes otherwise SQL does math on them: 2010-04-1 = 2004

sqlneofito
Starting Member

16 Posts

Posted - 2010-04-01 : 18:39:35

Thank you Lamprey I'll give it a try!

sqlneofito
Starting Member

16 Posts

Posted - 2010-04-01 : 21:23:50

single quotes, it worked!! thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 04:39:59

Also use unambigious YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-02 : 05:02:42

quote:
Originally posted by Lamprey

I see the issue.. you need to wrap the dates in single quotes otherwise SQL does math on them: 2010-04-1 = 2004

This is a correct measure, however your math example evaluates as 2005, right?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources