March 23, 2009 at 1:36 pm
I'm querying the number of weekdays between two dates unless the leavedate is null in which case I want the difference between the enterdate and today's date.
Select (case when (leavedate is null)
then datediff (day,enterdate,[date])
else datediff (day,enterdate,leavedate) as 'totaldays'
from reservations
I get "Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'."
Any thoughts would be helpful on this...
March 23, 2009 at 1:38 pm
else datediff (day,enterdate,leavedate) end as 'totaldays'
or just do:
select datediff (day,enterdate,coalesce(leavedate,[date])) as 'totaldays'
March 23, 2009 at 2:18 pm
select datediff (day,enterdate,coalesce(leavedate,[date])) as 'totaldays'
from reservations
produces the error...
"Invalid column name 'date'."
Select (case when (leavedate is null)
then datediff (day,enterdate,[date])
else datediff (day,enterdate,leavedate) end as 'totaldays'
from reservations
produces
Incorrect syntax near the keyword 'as'.
March 23, 2009 at 2:27 pm
You are missing the "end" for the case statement in your original code.
try one of these:
select
(case when (leavedate is null)
then datediff (day,enterdate,getdate())
else datediff (day,enterdate,leavedate)
end) as 'totaldays'
from
reservations
-- or
select
datediff(dd, enterdate, isnull(leavedate,getdate())) as totaldays
from
reservations
March 23, 2009 at 2:28 pm
SQL33 (3/23/2009)
Select (case when (leavedate is null)then datediff (day,enterdate,[date])
else datediff (day,enterdate,leavedate) end as 'totaldays'
from reservations
produces
Incorrect syntax near the keyword 'as'.
Count your brackets.
select datediff (day,enterdate,coalesce(leavedate,[date])) as 'totaldays'
from reservations
produces the error...
"Invalid column name 'date'."
What is [date] in you script then?
_____________
Code for TallyGenerator
March 23, 2009 at 2:28 pm
Do you not have a column named Date like you showed in your original query? If not, you will have to fill in the correct column name.
You have unbalanced () around your case statement; there is an extra ( at the beginning.
March 23, 2009 at 2:29 pm
You supplied the original query with the column defined as [date]. If you don't have that column available, what do you want the [date] column to be?
I am guessing here - you can read the article I link to in my signature if you need additional help:
SELECT DATEDIFF(day, enterdate, COALESCE(leavedate, getdate())) AS TotalDays
FROM dbo.Reservations;
Instead of using a column called [date] - replace it with the getdate function.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 23, 2009 at 2:32 pm
Should be getdate() not [date]
Select case when (leavedate is null)
then datediff (day,enterdate,getdate())
else datediff (day,enterdate,leavedate)
end as 'totaldays'
from reservations
-- You can't be late until you show up.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy