| Author |
 Topic  |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
 Posted - 07/23/2012 : 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.
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/23/2012 : 09:33:22
|
Can you double check? It should return only number
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 07/23/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 07/23/2012 : 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
India
47023 Posts |
Posted - 07/23/2012 : 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
United Kingdom
517 Posts |
Posted - 07/23/2012 : 10:04:57
|
| thank you very much for your help. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/24/2012 : 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
India
47023 Posts |
Posted - 07/24/2012 : 09:33:10
|
quote:
Originally posted by masterdineen
thank you very much for your help.
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
datediff issue
