datediff issue - 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

General SQL Server Forums

New to SQL Server Programming

datediff issue

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-23 : 08:46:30

Hello there.

I want to use the datediff function but i also require a variable which will be the month.

so i have created the following stored proc.



alter proc RenewalDateTest

@month	varchar(2)
as
declare @date	datetime

declare @current datetime

select @date = (select convert(varchar(4),datepart(year,getdate()))+'-'+@month+'-'+convert(varchar(2),datepart(day,getdate()))+' '+'00:00:00.000')

select @current = (select getdate())

select datediff(day,@date,@current)

go

exec RenewalDateTest 3

result = 1900-05-03 00:00:00.000

my question is why am i not getting a datediff in days.





[code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-23 : 09:33:22

Can you double check? It should return only number

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-23 : 09:38:25

within the stored proc

i was selecting

select @date

union all

select datediff(day,@date,@current)

so i removed the select @date and now it works.

strange. shouldnt it still return the day difference

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-23 : 09:42:57

UNION returns ONE record set and in this record set there can be only ONE datatype for a column hence an implicit conversion has happened.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:02:12

quote:
Originally posted by masterdineen

within the stored proc

i was selecting

select @date

union all

select datediff(day,@date,@current)

so i removed the select @date and now it works.

strange. shouldnt it still return the day difference

that explains it

since first value was of datetime type it converted second integer value returned by datediff also to datetime time. so in effect what you get will be integer amount of days added to base date which is 1900-01-01 00:00:00.
see below to understand how date manipulation works with integer values

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-07-23 : 10:04:57

thank you very much for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-24 : 04:46:00

quote:
Originally posted by masterdineen

within the stored proc

i was selecting

select @date

union all

select datediff(day,@date,@current)

so i removed the select @date and now it works.

strange. shouldnt it still return the day difference

You should have posted this in your original question itself

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 09:33:10

quote:
Originally posted by masterdineen

thank you very much for your help.

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources