|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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 Show quote "Chris Lane" <chris.l***@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
Show quote "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.l***@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? >> >> >> > >
Show quote
"Chris Lane" <chris.l***@lantic.net> wrote in message Avoid putting the CONVERT on the column. Instead, an expression like the news:dl401b$lkt$1@ctb-nnrp2.saix.net... > 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.l***@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? >>> >>> >>> >> >> > > 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_TIMESTAMP),112) ; -- David Portas SQL Server MVP -- Thanks David
Most helpful regards Chris Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:kpednTVQCsA5X-jeRVnyhA@giganews.com... > "Chris Lane" <chris.l***@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... >>> 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.l***@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? >>>> >>>> >>>> >>> >>> >> >> > > 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_TIMESTAMP),112) ; > > -- > David Portas > SQL Server MVP > -- > >  |
|||||||||||||||||||||||
Other interesting topics