CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
SOLDIER-OF-FORTUNE
SOLDIER-OF-FORTUNE

Reputation: 1654

DATEADD and BETWEEN not working

I have the following code:

DECLARE @testDate date
--SET @testDate=dateadd(wk, 5830, 0)
SET @testDate='2011-09-26 00:00:00.000'

SELECT *
FROM dbo.timesheets
WHERE @testDate BETWEEN convert(datetime, start_dtm, 120) and convert(datetime, end_dtm, 120)

PRINT @testDate

It should convert the value 5830 back to the date format and then check between start_dtm and end_dtm

I have tested this by hard coding the value from the database in the above code and i still dont see any records. The printed @testdate shows up like this: 2011-09-26

I am creating a week_ref which i am passing via querystring. The timesheets report will then convert the week_ref eg, '5830' to the date and check if its between the start_dtm and end_dtm. In theory it should display results as its checking if values are between start and end.

Any ideas? or help on debugging?

EDIT: row in the timesheets table:

Example row from timesheets table

Here is the query i am trying to get working:

DECLARE @week_ref INT
DECLARE @weekDate date

set @weekDate=dateadd(wk,@week_ref,0)



SELECT     ts.staff_member_ref, sm.common_name, sm.department_name, DATENAME(MONTH, ts.start_dtm) + ' ' + DATENAME(YEAR, ts.start_dtm) AS month_name, 
                      ts.timesheet_cat_ref, cat.desc_long AS timesheet_cat_desc, grps.grouping_ref, grps.description AS grouping_desc, ts.task_ref, tsks.task_code, 
                      tsks.description AS task_desc, ts.site_ref, sits.description AS site_desc, ts.site_ref AS Expr1, 
                      CASE WHEN ts .status = 0 THEN 'Pending' WHEN ts .status = 1 THEN 'Booked' WHEN ts .status = 2 THEN 'Approved' ELSE 'Invalid Status' END AS site_status, 
                      ts.booked_time AS booked_time_sum,

start_dtm, CONVERT(varchar(20), start_dtm, 108) + ' ' + CONVERT(varchar(20), start_dtm, 103) AS start_dtm_text, booked_time,
end_dtm, CONVERT(varchar(20), end_dtm, 108) + ' ' + CONVERT(varchar(20), end_dtm, 103) AS end_dtm_text


FROM timesheets AS ts 

INNER JOIN timesheet_categories AS cat ON ts.timesheet_cat_ref = cat.timesheet_cat_ref 
INNER JOIN timesheet_tasks AS tsks ON ts.task_ref = tsks.task_ref 
INNER JOIN timesheet_task_groupings AS grps ON tsks.grouping_ref = grps.grouping_ref 
INNER JOIN timesheet_sites AS sits ON ts.site_ref = sits.site_ref 
INNER JOIN vw_staff_members AS sm ON ts.staff_member_ref = sm.staff_member_ref
--INNER JOIN week_list AS WL ON ts.timesheet_ref = WL.week_ref

--WHERE (ts.status IN (1, 2)) AND (cat.is_leave_category = 0)
--AND WL.week_ref=@week_ref AND WL.start_week BETWEEN ts.start_dtm AND ts.end_dtm

WHERE @weekDate <= convert(datetime, end_dtm, 120) 
AND @weekDate > convert(datetime, start_dtm, 120)



GROUP BY ts.staff_member_ref, sm.common_name, sm.department_name, DATENAME(MONTH, ts.start_dtm), DATENAME(YEAR, ts.start_dtm), ts.timesheet_cat_ref, 
                      cat.desc_long, grps.grouping_ref, grps.description, ts.status, ts.booked_time, ts.task_ref, tsks.task_code, tsks.description, ts.site_ref, sits.description, ts.start_dtm, 
                      ts.end_dtm
ORDER BY sm.common_name, timesheet_cat_desc, tsks.task_code, site_desc

Upvotes: 0

Views: 3073

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

I have 2 queries that i want you to consider:

declare @timesheets table(start_dtm datetime, end_dtm datetime)

insert @timesheets values('2011-09-26', '2011-09-27')
insert @timesheets values('2011-09-28', '2011-09-29')


-- This will return 1 row
SELECT *
FROM @timesheets
WHERE dateadd(wk, 5830, 0) BETWEEN start_dtm and end_dtm

-- This will return both rows
SELECT *
FROM @timesheets
WHERE 5830 BETWEEN datediff(wk, 0, start_dtm) and datediff(wk, 0, end_dtm)

You should avoid converting/casting when it isn't nessasary.

Upvotes: 2

user359040
user359040

Reputation:

I think you actually want:

DECLARE @testDate date
SET @testDate=dateadd(wk, 5804, 0)
--SET @testDate='2011-03-28 00:00:00.000'

SELECT *
FROM dbo.timesheets
WHERE @testDate <= convert(datetime, end_dtm, 120) and 
      dateadd(wk, 1, @testDate) > convert(datetime, start_dtm, 120)

PRINT @testDate

This version of the query tests whether any part of the records' date ranges is within the week beginning on @testDate.

Upvotes: 2

Hogan
Hogan

Reputation: 70538

Based on edit this should work fine:

DECLARE @testDate datetime
SET @testDate='2011-09-26 00:00:00.000'

SELECT *
FROM dbo.timesheets
WHERE @testDate BETWEEN start_dtm and end_dtm

or you might have this problem (there is no time info and you just have one day:

     SELECT *
     FROM dbo.timesheets
     WHERE @testDate BETWEEN start_dtm and end_dtm 
  OR @testDate = start_dtm OR @testDate = end_dtm

Some variables are date type and some are datetime. I would do it like this

DECLARE @testDate date
SET @testDate='2011-09-26 00:00:00.000'

SELECT *
FROM dbo.timesheets
WHERE @testDate BETWEEN cast(start_dtm as date) and cast(end_dtm as date)

or 

DECLARE @testDate datetime
SET @testDate='2011-09-26 00:00:00.000'

SELECT *
FROM dbo.timesheets
WHERE @testDate BETWEEN cast(start_dtm as datetime) and cast(end_dtm as datetime)

Upvotes: 1

Related Questions