Date criteria for SQL

Question

0.00/5 (No votes)

See more:

Hello, SQL Server 2008 R2
It is Friday and my brain hurts. I want to find all records for yesterday so I have

SELECT * FROM myTable 
WHERE myDateTime = '1/27/2011'

This will not do it as the records have the time. Thus what I would like is to do something like

WHERE CAST(myDateTime AS DATE) = '1/27/2011'

however this kills indexing.

So do I need to use something like

WHERE myDateTime BETWEEN '1/27/2011' AND '1/28/2011'

Thank you,
djj

Posted 28-Jan-11 2:19am

Corporal Agarn

Add a Solution

3 solutions

Solution 3

If you wanted to do it using a single date, you could do something like this

SQL

select
    *
FROM
    SomeTable
WHERE
    CAST(FLOOR( CAST( YourField  AS FLOAT ) )AS DATETIME) = '1/27/2011'

So, strip out the time from the field and then compare to some value

Update: sorry, I saw your post about that killing indexes

Otherwise, the only way to do it is to use a BETWEEN.

SQL

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = CAST(FLOOR( CAST( GETDATE-1  AS FLOAT ) )AS DATETIME)
SET @EndDate = CAST(FLOOR( CAST( GETDATE  AS FLOAT ) )AS DATETIME)
select
    *
FROM
    SomeTable
WHERE
    YourField BETWEEN @StartDate AND @EndDate

Posted 28-Jan-11 2:54am

Dylan Morley

Updated 28-Jan-11 2:57am

v2

Comments

Corporal Agarn 28-Jan-11 9:06am

Thanks for the suggestion. I will be doing something similar. (See Prerak Patel's answer)

Espen Harlinn 29-Jan-11 4:52am

5+ Good work :)

Solution 1

What about:

WHERE myDateTime >= '1/27/2011'

Posted 28-Jan-11 2:27am

Sandeep Mewara

Comments

Corporal Agarn 28-Jan-11 8:31am

This would give me records from today. Thank you for the answer.

Sandeep Mewara 28-Jan-11 9:04am

Ya, just suggested to use >= and <= operators if that works for you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Accepted Answer · 2011-01-28T02:28:00

Solution 2

WHERE myDateTime >= '1/27/2011' and  myDateTime < '1/28/2011'

[Edit:]
*considering our discussion

WHERE (CONVERT(nvarchar, myDateTime, 101) = '01/27/2011')

Posted 28-Jan-11 2:28am

Prerak Patel

Updated 28-Jan-11 3:03am

v3

Comments

Corporal Agarn 28-Jan-11 8:31am

Would this be different from the BETWEEN?

Prerak Patel 28-Jan-11 8:39am

Don't you get the records you want exactly!? or you face any problem?

Corporal Agarn 28-Jan-11 8:45am

What I had hoped for was a way to do it with a single date instead of two as this will be coded to retrieve data from the previous work day. Thanks though

Prerak Patel 28-Jan-11 8:52am

Oh I thought this a good way because with between you will get the records dated midnight i.e. 20-Jan-2011 00:00 if exists.

Prerak Patel 28-Jan-11 9:01am

WHERE (CONVERT(nvarchar, myDateTime, 101) = '01/27/2011') ??

Corporal Agarn 28-Jan-11 9:03am

Good thought. The data I am working with should not have anything after 10PM but your point is well taken and I think I will try your suggestion.