?
Solved

SELECt with a Date not Working

Posted on 2011-05-10
6
Medium Priority
?
231 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:jaymz69
6 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35734048
Try this instead:

SET @TodayDate = CONVERT(int,GETDATE())
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35734055
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35734060
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 9

Expert Comment

by:anillucky31
ID: 35734983
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35736169
Where -> Replace(CONVERT(varchar, @TodayDate,102),'.','-')
0
 

Author Closing Comment

by:jaymz69
ID: 35739508
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question