Query in SQL Server 2008 not working in SQL Server 2005

Question

DECLARE @MyDate Datetime 
set @MyDate = GETDATE();
WITH cte AS
 (SELECT @MyDate AS AllDates,
       1 AS [count_all_days],
       CASE WHEN DATENAME(dw, DATEADD(dd, - 1, @MyDate)) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END AS [count_week_days]
UNION ALL
SELECT DATEADD(dd, - [count_all_days], @MyDate), 
       [count_all_days] + 1,
       CASE WHEN DATENAME(dw, DATEADD(dd, - [count_all_days], @MyDate)) IN ('Saturday', 'Sunday') THEN [count_week_days] + 1 ELSE [count_week_days] 
       END
FROM cte
WHERE [count_all_days] - [count_week_days] < 16 
)

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, AllDates))
FROM cte left join EmpLog ON AllDates = EmpLog.Date 
           left JOIN Holiday ON AllDates = Holiday.HolDate
WHERE DATENAME(dw, AllDates) NOT IN ('Saturday', 'Sunday')AND AllDates NOT IN (Select EmpLog.Date from EmpLog where EmpLog.EmpID = 1)and Holiday.HolDate IS NULL

This SQL query displays the current date and the previous 16 days. wherein those dates are not equal to the existing date on the HOLIDAY table and in the EMPLOG table.

My problem is this query works on SQL Server 2008 well but when I tried it on SQL Server 2005 it only displays the current date and the previous 16 days even though some days are in HOLIDAY and EMPLOG table.

Can someone help me please? thanks!

DMK · Accepted Answer · 2013-03-25 16:55:24Z

1

Try casting your Date variables -- you're comparing GetDate to Dates that don't necessarily have the same time:

left JOIN Holiday ON CAST(AllDates as Date) =  CAST(Holiday.HolDate as Date)

Wrap that with all your dates and it should work. Take a look at this SQL Fiddle. In this example, I've only added 1 holiday, 1/21/2013 (MLK).

EDIT --

Try using convert(varchar, getdate(), 101) to CAST the date since SQL Server 2005 doesn't support the Date type.

Here is the updated SQL:

left JOIN Holiday ON convert(varchar, AllDates, 101) =  convert(varchar, Holiday.HolDate, 101)

Do that on all your date conversions. Here is the updated Fiddle.

Thanks @user148298 for pointing this out.

Good luck.

edited Mar 25, 2013 at 16:55

DMK

2,5371 gold badge26 silver badges36 bronze badges

answered Jan 29, 2013 at 3:47

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ATL_DEV Over a year ago

SQL 2005 doesn't support Date. msdn.microsoft.com/en-us/library/ms187752%28v=sql.90%29.aspx

sgeddes Over a year ago

You can CAST that as any date... Try using convert(varchar, getdate(), 101)

jaycel aquino Over a year ago

when i tried that code i get an error Type Date is not a defined system type. so i change the Date to Datetime but when i execute the query it sill displays the Holiday date.

jaycel aquino Over a year ago

sorry for late reply. yes i see your edited query. i'll try it first.

jaycel aquino Over a year ago

@sgeddes since i have 2 joins in my select statement EMPLOG and HOLIDAY, i also try your solution to the other join:

left join EmpLog ON convert(varchar, AllDates, 101) = convert(varchar, EmpLog.Date, 101)                 left JOIN Holiday ON convert(varchar, AllDates, 101) = convert(varchar, Holiday.HolDate, 101)

but when i execute the query the output still displays the date in the EMPLOG table.

|

ATL_DEV · Accepted Answer · 2013-01-29 03:45:51Z

0

There appears to be some differences between dates in 2008 and 2005. You have to set your database compatibility. See the following article:

http://msdn.microsoft.com/en-us/library/bb510680.aspx

answered Jan 29, 2013 at 3:45

ATL_DEV

9,65813 gold badges68 silver badges111 bronze badges

Comments

cha · Accepted Answer · 2013-01-29 04:29:11Z

0

Try changing the @MyDate initialisation to:

set @MyDate = Convert(DATETIME(CONVERT(VARCHAR(10), GETDATE(), 121) + ' 00:00:00', 121);

Explanation: By doing this you make the @MyDate as a "midnight today", instead of "today with current time". That way it would better join with your tables.

Assumption: The dates in your holiday tables are stored as "midnight"

edited Jan 29, 2013 at 4:29

answered Jan 29, 2013 at 3:25

cha

10.4k1 gold badge20 silver badges27 bronze badges

3 Comments

jaycel aquino Over a year ago

thanks for your answer. i think this is the part of the query that is not workng because i tried to remove and put this query and nothing happens, same output. "AllDates NOT IN (Select EmpLog.Date from EmpLog where EmpLog.EmpID = 1)and Holiday.HolDate IS NULL"

cha Over a year ago

Just run a simple select on both of your servers and compare result: select datepart(dw, getdate()), datename(dw, getname())

cha Over a year ago

I know that, thanks. But what I asked is to run it on your both servers: 2008 and 2005. Please check that on both servers the results are the same

Collectives™ on Stack Overflow

Query in SQL Server 2008 not working in SQL Server 2005

3 Answers 3

9 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related