Date part of a dateTime value

Hi,
This is no function in SQL Server to get only the date part of a datetime
variable, sometime, and usually most of the time, I would like to have a
query to get the result of only some specific day's, and I am not sure what
could be the best way to do this query.
For example, if I want to get all the records of my table for only todays.
Thanks in advance for your advice on this.
FrankYou can get the Date part of the date time using the datepart function.
select Convert(nvarchar(25),getdate(),100 )
You can use the datediff function for retrieving the current day's
record
Select * from table where datediff(d,getdate(),datecolumn) = 0|||Hi,
SELECT * from YOurTable
Where Datefield >= convert(VARCHAR(8),getdate(),112)
Uses an ISO Date format, you could also Convert the Datefield Column, but
this wont need to be neccessary because of causing performance issues.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
> Frank
>|||Continuation to the above message from me ...
Sorry .. use this
select Convert(nvarchar(25),getdate(),101 )
you can also check books online for the different values instead of 101
for getting the required output|||Thanks, balacr,
The second datediff(d, getdate(), datecolumn) = 0 is exactly what I want.
B/R
Frank
<balacr@.gmail.com> wrote in message
news:1113806479.336151.291970@.l41g2000cwc.googlegroups.com...
> You can get the Date part of the date time using the datepart function.
> select Convert(nvarchar(25),getdate(),100 )
> You can use the datediff function for retrieving the current day's
> record
> Select * from table where datediff(d,getdate(),datecolumn) = 0
>|||Hi Frank,
"Frank" <wangping@.lucent.com> wrote in message
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
Use Northwind
/* (1) */
select * from orders
where
orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
DATETIME))
/* (2) */
select * from orders
where
day(orderdate) = day(getdate()) and
month(orderdate) = month(getdate()) and
year(orderdate) = year(getdate())
/* (3) */
Select * from orders
where
datediff(day ,getdate() , orderdate) = 0

> Frank
HTH,
Andrea|||Thanks, Jens,
I also noticed that the "where datediff()" method could causing performance
issues, since I was told that if in the "where clause", there is some
functions on the column, then even there is an index on that column, the
database query engine will not use it.
That is something like this,
select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
Then even on "myDateColumn" there is an index, the SQL Server will not use
that index.
Am I right?
B/R
Frank
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> SELECT * from YOurTable
> Where Datefield >= convert(VARCHAR(8),getdate(),112)
> Uses an ISO Date format, you could also Convert the Datefield Column, but
> this wont need to be neccessary because of causing performance issues.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
>|||Yeah you are, thats why i pointed out not to do something with the column
rather than doing this "static" Conversion (because it will be only
evaluated once.)
HTH, Jens Smeyer.
http:/www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:uKcZMQ%23QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thanks, Jens,
> I also noticed that the "where datediff()" method could causing
> performance
> issues, since I was told that if in the "where clause", there is some
> functions on the column, then even there is an index on that column, the
> database query engine will not use it.
> That is something like this,
> select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
> Then even on "myDateColumn" there is an index, the SQL Server will not use
> that index.
> Am I right?
> B/R
> Frank
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> datetime
> todays.
>|||Thanks, Andrea,
I would like to choose the first of your ideas, I think there maybe some
performance issues for the second and third ones.
B/R
Frank
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:%232XsyM%23QFHA.3076@.tk2msftngp13.phx.gbl...
> Hi Frank,
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
> Use Northwind
> /* (1) */
> select * from orders
> where
> orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
> orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
> DATETIME))
> /* (2) */
> select * from orders
> where
> day(orderdate) = day(getdate()) and
> month(orderdate) = month(getdate()) and
> year(orderdate) = year(getdate())
> /* (3) */
> Select * from orders
> where
> datediff(day ,getdate() , orderdate) = 0
>
> HTH,
> Andrea
>sql