quick SELECT statement question

14 May 2005 4:59 PM

Rudy

Hello all!
I have a date/time column on my table. How can I write a select statement
to pick only items from todays date? I looked, but can't find an answer.
SELECT * User FROM TABLE WHERE datecolumn = "todays date"
Thanks!
Rudy

14 May 2005 5:13 PM

Mike Epprecht (SQL MVP)

SELECT
                *
FROM
                Table
WHERE
                CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
GETDATE(), 112)

Due to the time that is stored in a datatime datatype, you need to strip the
time out as above.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Rudy" <R***@discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy

14 May 2005 5:58 PM

Rudy

Hi Mike!

WOW!! I wouild have never figured that out. Thanks!!

Rudy

Show quote

"Mike Epprecht (SQL MVP)" wrote:

> SELECT
>                 *
> FROM
>                 Table
> WHERE
>                 CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
> GETDATE(), 112)
>
> Due to the time that is stored in a datatime datatype, you need to strip the
> time out as above.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: m***@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "Rudy" <R***@discussions.microsoft.com> wrote in message
> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@microsoft.com...
> > Hello all!
> > I have a date/time column on my table. How can I write a select statement
> > to pick only items from todays date? I looked, but can't find an answer.
> > SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> > Thanks!
> > Rudy
>
>
>

14 May 2005 6:24 PM

Mike Epprecht (SQL MVP)

Hi

Or

SELECT
                 *
FROM
                Table
WHERE
                datecolumn >= (CONVERT(CHAR(8), GETDATE(), 112) + '
00:00:00.000'
                AND datecolumn <= (CONVERT(CHAR(8), GETDATE(), 112) + '
23:59:59.997'

The 1st one can not use an index if that is the only predicate in the where
clause as each row needs to be evaluated.
The 2nd one could use an index, but make sure that you use ' 23:59:59.997'
and not ' 23:59:59.999' as .999 can not be represented in datetime, so it
rounds itself to 00:00:00.000, the next day

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Rudy" <R***@discussions.microsoft.com> wrote in message
news:2202B61C-5A3A-4FEB-B0E4-6FC0480FB1EA@microsoft.com...
> Hi Mike!
>
> WOW!! I wouild have never figured that out. Thanks!!
>
> Rudy
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> SELECT
>>                 *
>> FROM
>>                 Table
>> WHERE
>>                 CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
>> GETDATE(), 112)
>>
>> Due to the time that is stored in a datatime datatype, you need to strip
>> the
>> time out as above.
>>
>> Regards
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: m***@epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Rudy" <R***@discussions.microsoft.com> wrote in message
>> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@microsoft.com...
>> > Hello all!
>> > I have a date/time column on my table. How can I write a select
>> > statement
>> > to pick only items from todays date? I looked, but can't find an
>> > answer.
>> > SELECT * User FROM TABLE WHERE datecolumn = "todays date"
>> > Thanks!
>> > Rudy
>>
>>
>>

15 May 2005 4:47 PM

Tibor Karaszi

In addition to Mike's comments, you might want to red more about the subject at:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Rudy" <R***@discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy