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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply