CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005
jaycel aquino
jaycel aquino

Reputation: 1

Query in SQL Server 2008 not working in SQL Server 2005

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!

Upvotes: 0

Views: 431

Answers (3)

sgeddes
sgeddes

Reputation: 62851

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.

Upvotes: 1

cha
cha

Reputation: 10411

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"

Upvotes: 0

ATL_DEV
ATL_DEV

Reputation: 9591

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

Upvotes: 0

Related Questions