Scott Berry wrote:
Hi,
I have an ADP file that stores dates in a datetime field type and I want to
query the table for all records for a single date (ie. ignore the time). I
want it to automatically return all records for a single day that is 7 days
prior to todays date.(in dd/mm/yyyy format)
I have looked in books on-line, and have had some success with the CONVERT
function, but I cannot get the results consistently as dd/mm/yyyy format.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Perhaps:
WHERE date_column BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),101)) - 7
AND (CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),101)) - 7) + 1
The CONVERT(VARCHAR(10)...) turns the date-time into the date only.
The last conversion that adds 1 to the result is used to span the date 7
days previous to the current date. By using a span of date-times you
get all times in one date. I.e., Midnight (00:00) to the next midnight
(24:00).
The same example w/ conversions (getdate() returns 12 Feb 2005):
WHERE date_column BETWEEN '2005-02-05 12:00AM' And '2005-02-06 12:00AM'
To display in "dd/mm/yyyy" format you'll have to use the CONVERT()
function in the SELECT clause:
SELECT CONVERT(VARCHAR(10), date_column, 103) As date_value, ...
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQg5T64echKqOuFEgEQJCmQCg8SA8qB+FrzQ0L3F5ApK3Aj EVgaoAoMp5
7WQPe+uqKqbrkngRKJz4vvEg
=4BCU
-----END PGP SIGNATURE-----