Solved

# SELECt with a Date not Working

Posted on 2011-05-10
220 Views
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',
irdate 'Date',
iritem 'item',
irdesc 'Description',
irbin 'Bin',
irqty 'QTY',
ircost 'Cost',
iruser 'User'
(irqty * ircost) 'Total Cost'

FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE irreason IN(''02'', ''25'', ''16'', ''38'', ''39'', ''PI'')
')

WHERE irdate = @TodayDate
ORDER BY irloc ASC
``````
0
Question by:jaymz69

LVL 33

Expert Comment

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

LVL 32

Accepted Solution

try this
``````DECLARE @TodayDate datetime
SET @TodayDate = dateadd(dd, 0, datediff(dd, 0, GETDATE()))

-- Query
SELECT irloc 'Location',
irdate 'Date',
iritem 'item',
irdesc 'Description',
irbin 'Bin',
irqty 'QTY',
ircost 'Cost',
iruser 'User'
(irqty * ircost) 'Total Cost'

FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE irreason IN(''02'', ''25'', ''16'', ''38'', ''39'', ''PI'')
')

WHERE irdate = @TodayDate
ORDER BY irloc ASC
``````
0

LVL 33

Expert Comment

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

LVL 9

Expert Comment

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',
irdate 'Date',
iritem 'item',
irdesc 'Description',
irbin 'Bin',
irqty 'QTY',
ircost 'Cost',
iruser 'User'
(irqty * ircost) 'Total Cost'

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
``````
0

LVL 21

Expert Comment

Where -> Replace(CONVERT(varchar, @TodayDate,102),'.','-')
0

Author Closing Comment

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

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, …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…