I am new to SQL and I am having problems getting the result I need from a
datediff function.
What I am trying to do is check the difference between to dates for which a
customer rented a product and if they only rented for one day bring them up
in the result set.
For example if you have the following:
DateRented 4/26/08
DateReturned 4/26/08
I want the value to come up as 1 instead of 0 is there any way to perform
this function in this manner?
Thanks in advance for any help
NealHi Neal
For this one example, you can add 1 to the datediff
SELECT datediff(dd, datereturned, daterented) + 1
Datediff counts the number of boundaries of the date unit passed as the
first argument. Since these two dates do not cross a day boundary, the
result is 0.
But you need to think about the general case before we can help with a
complete solution. Does the time of day matter?
If DateRented is April 26 and DateReturned is April 28, do you want to
return 3?
You should also consider using a completely unambiguous date format.
4/26/08 might be obvious because there is no 26th month, but many people
will interpret 5/1/08 as January 1st.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Neal Ostrander" <NealOstrander@.discussions.microsoft.com> wrote in message
news:192562E3-DA26-48C6-BA28-FD7DAFB10DF7@.microsoft.com...
>I am new to SQL and I am having problems getting the result I need from a
> datediff function.
> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
> For example if you have the following:
> DateRented 4/26/08
> DateReturned 4/26/08
> I want the value to come up as 1 instead of 0 is there any way to perform
> this function in this manner?
> Thanks in advance for any help
> Neal|||> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
If the data contains date only (midnight time), try:
WHERE
DATEADD(day, 1, DATEDIFF(day, DateRented, DateReturned)) = 1
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Neal Ostrander" <NealOstrander@.discussions.microsoft.com> wrote in message
news:192562E3-DA26-48C6-BA28-FD7DAFB10DF7@.microsoft.com...
>I am new to SQL and I am having problems getting the result I need from a
> datediff function.
> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
> For example if you have the following:
> DateRented 4/26/08
> DateReturned 4/26/08
> I want the value to come up as 1 instead of 0 is there any way to perform
> this function in this manner?
> Thanks in advance for any help
> Neal|||The code depends on what you want when the date returned is one day
later than the date rented. I will assume that too should be 1.
SELECT CASE WHEN DATEDIFF(day,DateRented,DateReturned) = 0
THEN 1
ELSE DATEDIFF(day,DateRented,DateReturned)
END
FROM Whatever
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 18:04:40 -0700, Neal Ostrander
<NealOstrander@.discussions.microsoft.com> wrote:
>I am new to SQL and I am having problems getting the result I need from a
>datediff function.
>What I am trying to do is check the difference between to dates for which a
>customer rented a product and if they only rented for one day bring them up
>in the result set.
>For example if you have the following:
>DateRented 4/26/08
>DateReturned 4/26/08
>I want the value to come up as 1 instead of 0 is there any way to perform
>this function in this manner?
>Thanks in advance for any help
>Neal
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment