SELECt with a Date not Working

For some reason this SELECT just seems to do nothing when I press Debug and Continue

But If I plug in a date
irdate='2011-05-10'

it works

What am I missing?
DECLARE @TodayDate datetime
SET @TodayDate = CONVERT(date,GETDATE())

-- Query
SELECT irloc 'Location',
	irreason 'Adj Code',
	irdate 'Date',
	iritem 'item',
	irdesc 'Description',
	irbin 'Bin',
	ircomt 'Comments',
	irqty 'QTY',
	ircost 'Cost',
	iruser 'User'
	(irqty * ircost) 'Total Cost'
	
-- Select Adjustmenr reason codes
FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE irreason IN(''02'', ''25'', ''16'', ''38'', ''39'', ''PI'')
')

WHERE irdate = @TodayDate 
ORDER BY irloc ASC

Open in new window

jaymz69Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
try this
DECLARE @TodayDate datetime
SET @TodayDate = dateadd(dd, 0, datediff(dd, 0, GETDATE()))

-- Query
SELECT irloc 'Location',
	irreason 'Adj Code',
	irdate 'Date',
	iritem 'item',
	irdesc 'Description',
	irbin 'Bin',
	ircomt 'Comments',
	irqty 'QTY',
	ircost 'Cost',
	iruser 'User'
	(irqty * ircost) 'Total Cost'
	
-- Select Adjustmenr reason codes
FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE irreason IN(''02'', ''25'', ''16'', ''38'', ''39'', ''PI'')
')

WHERE irdate = @TodayDate 
ORDER BY irloc ASC

Open in new window

0
 
knightEknightCommented:
Try this instead:

SET @TodayDate = CONVERT(int,GETDATE())
0
 
knightEknightCommented:
The GETDATE function returns the current date AND time.  The date portion is stored before the decimal and the time portion is stored after, so if you convert a datetime (or GETDATE() ) to an int, you are left with just the date portion.  Therefore, you get '2011-05-10 00:00:00' instead of '2011-05-10 18:48:27', which GETDATE returns by default.  :)
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
anillucky31Commented:
Hi, you just need to modify where condition.

in where condition just do this.

WHERE DATEDIFF(d, irdate, @TodayDate) = 0

it will compare datepart only and you will get you desired result.
I have attached the code


DECLARE @TodayDate datetime
SET @TodayDate = CONVERT(date,GETDATE())

-- Query
SELECT irloc 'Location',
	irreason 'Adj Code',
	irdate 'Date',
	iritem 'item',
	irdesc 'Description',
	irbin 'Bin',
	ircomt 'Comments',
	irqty 'QTY',
	ircost 'Cost',
	iruser 'User'
	(irqty * ircost) 'Total Cost'
	
-- Select Adjustmenr reason codes
FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE irreason IN(''02'', ''25'', ''16'', ''38'', ''39'', ''PI'')
')

 WHERE DATEDIFF(d, irdate, @TodayDate) = 0
  
ORDER BY irloc ASC

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Where -> Replace(CONVERT(varchar, @TodayDate,102),'.','-')
0
 
jaymz69Author Commented:
since there are so many records in this file (from the AS400) the query just seems to be running and does nothing else.

no final result.

It is like it is trying to do ALL the work first then find the date.

How can I get it to quickly go after the date
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.