format time to 00 for a date

hi,
How do I format time of a datecolumn to 00:00:00 ?
I have a table with date column. how do I get only date for comparision, and
exclude or convert time to 00:00:00 in sql query
regards
ypulTry this and you will see what you can do:
select dateadd(dd, 0, datediff(dd, 0, getdate()))
Perayu
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>|||> How do I format time of a datecolumn to 00:00:00 ?
Formatting is usually done at the client tier, but you can handle it using
CONVERT with a style parameter, if you must.
SELECT CONVERT(CHAR(8), GETDATE(), 24)
For a complete list of styles, please see http://www.aspfaq.com/2464

> I have a table with date column. how do I get only date for comparision,
> and
> exclude or convert time to 00:00:00 in sql query
If there is an index on the column, you should always a range query. (And
if you don't care about time, you might consider not storing it.)
For example, to get all the rows with a date of 2005-09-12 (regardless of
time), you can say:
WHERE date_column >= '20050912'
AND date_column < '20050913'
To do this dynamically (e.g. always yesterday),
DECLARE @.start SMALLDATETIME
SET @.start = DATEDIFF(DAY, 0, GETDATE()) - 1
SELECT
..
WHERE date_column >= @.start
AND date_column < @.start + 1
You may be tempted to use BETWEEN but please read the following article:
http://www.aspfaq.com/2280
You maybe tempted to use localized formats for dates (e.g. d/m/y or m/d/y)
but please read the following article and the links therein:
http://www.aspfaq.com/2023|||Basically, what you'd do is to format your data on the client, but if you
insist on doing it on the server, read more here:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML|||Thanks a lot all
"Between" article was also useful...
gr8 help
ypul
"ypul" <ypul@.hotmail.com> wrote in message
news:OVD9DHGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> hi,
> How do I format time of a datecolumn to 00:00:00 ?
> I have a table with date column. how do I get only date for comparision,
and
> exclude or convert time to 00:00:00 in sql query
> regards
> ypul
>
>
>