GETDATE

I have a database that contains, amongst others, the following fields
DocNo, DocDate, DocAmt
I need to extract the data in the these fields, but only where the date (in
yyyy-mm-dd format) is equal to the current date. In other words, I need a
schedule of documents produced on the day of running the query.
I am battling with the WHERE statement. Can anyone help please?Try this:
WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
This statement sets the time to 00:00:00 on both the sides.
Regards,
Peri
"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> I have a database that contains, amongst others, the following fields
> DocNo, DocDate, DocAmt
> I need to extract the data in the these fields, but only where the date
(in
> yyyy-mm-dd format) is equal to the current date. In other words, I need a
> schedule of documents produced on the day of running the query.
> I am battling with the WHERE statement. Can anyone help please?
>
>|||Thanks Peri
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Try this:
> WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
> CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
> This statement sets the time to 00:00:00 on both the sides.
> Regards,
> Peri
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> (in
>|||"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Thanks Peri
> "Peri" <Peri@.newsgroups.nospam> wrote in message
> news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
>
Avoid putting the CONVERT on the column. Instead, an expression like the
following is more likely to make better use of any index on docdate.
...
WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
David Portas
SQL Server MVP
--|||Thanks David
Most helpful
regards
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:kpednTVQCsA5X-jeRVnyhA@.giganews.com...
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Avoid putting the CONVERT on the column. Instead, an expression like the
> following is more likely to make better use of any index on docdate.
> ...
> WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
> --
> David Portas
> SQL Server MVP
> --
>