DATEDIFF Command error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATEDIFF Command error

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-27 : 19:33:46

I am using MS SQL 2005. I am having a problem with one of the commands. Mainly the datediff command.

If I use the following query, it returns no results:

select machine, operator, accum, status, errflg, prod_date from databack where datediff(d,prod_date,current_timestamp)=0

However if I change the query to the following, it returns the correct results:

select machine, operator, accum, status, errflg, prod_date from databack where shift = 1 and datediff(d,prod_date,current_timestamp)=0

I dont know why it seems to require two conditions on the where clause in this case.

Any help greatly appreciate. Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-27 : 21:10:28

this does not make sense. Can post some sample data of such scenario ?

KH
[spoiler]Time is always against us[/spoiler]

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-27 : 21:37:28

Well the one section is easy. If I use only datediff in the where clause I get nothing.

However if I add in the shift = 1 and datediff on the where clause then I get all the data from the table where both conditions are true. And if I check the data in the origional table the query works just fine.

Since I am at home now someone sent me a note saying to try adding in the qualifier in the datediff command. So I will try that and see where it goes. This was there recommendataion starting at the from clause.

FROM table as i where datediff(d,i.prod_date,current_timestamp) =0

I will post my findings and see if this helps.

quote:
Originally posted by khtan

this does not make sense. Can post some sample data of such scenario ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-27 : 22:17:58

that would not make any different at all


-- Preparing the data
declare @table table
(
	machine		int,
	shift		int,
	prod_date	datetime
)

declare	@today datetime

select	@today	= dateadd(day, datediff(day, 0, getdate()), 0)
insert into @table (machine, shift, prod_date)
select	10, 1, @today	union all
select	20, 1, @today	union all
select	30, 1, @today	

-- Query 1

select	*
from	@table
where	datediff(d, prod_date, current_timestamp) = 0

/*
machine     shift       prod_date                                              
----------- ----------- ------------------------------------------------------ 
10          1           2009-04-28 00:00:00.000
20          1           2009-04-28 00:00:00.000
30          1           2009-04-28 00:00:00.000

(3 row(s) affected)
*/

-- Query 2
select	*
from	@table
where	shift	= 1
and	datediff(d, prod_date, current_timestamp) = 0

/*
machine     shift       prod_date                                              
----------- ----------- ------------------------------------------------------ 
10          1           2009-04-28 00:00:00.000
20          1           2009-04-28 00:00:00.000
30          1           2009-04-28 00:00:00.000

(3 row(s) affected)
*/

-- Query 3
select	*
from	@table t
where	datediff(d, t.prod_date, current_timestamp) = 0

/*
machine     shift       prod_date                                              
----------- ----------- ------------------------------------------------------ 
10          1           2009-04-28 00:00:00.000
20          1           2009-04-28 00:00:00.000
30          1           2009-04-28 00:00:00.000

(3 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-28 : 10:01:46

Yes you were right it did not make a difference by putting that i. in from of the field name. However I did notice something that I must have over looked, and for this I am sorry, in my original posting. I dont know why I over looked it but thought I better get it involved in this discussion.

The auctual query I am working on has a LEFT OUTER JOIN for another table. Here is the actual query:

select i.machine, coalesce (a.ProdEmpFirstName,'') + ' ' + coalesce(a.ProdEmpLastName,'') , i.accum, i.status, i.errflg, i.prod_date from databack LEFT OUTER JOIN emptable as a on a.empid = i.operator where datediff(d,prod_date,current_timestamp)=0

Now Like I said if I try this query I get the error.

If I add shift=1 or for that matter have any integer value here then this query works.

If I change the operator from = to < , > , !=, etc. I get the error.

If I change the Join type from LEFT to RIGHT, I do not get the error but the results data is incorrect. And it works with just the DATEDIFF for the where clause.

If I remove the JOIN and the second table the DATEDIFF works. But in this case the data is imcomplete.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-28 : 11:35:40

It might be an issue of the left join. You might try this:

select 
	i.machine, 
	coalesce (a.ProdEmpFirstName,'') + ' ' + coalesce(a.ProdEmpLastName,''), 
	i.accum, 
	i.status, 
	i.errflg, 
	i.prod_date 
from 
	databack as i 
LEFT OUTER JOIN 
	emptable as a 
	on a.empid = i.operator 
	AND datediff(d,prod_date,current_timestamp)=0

If that doesn;t work can you expain in more detail what you are trying to acholmplish and we can help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources