Get only the Date part of DateTime in mssql

Question

Possible Duplicate    Get just the Date from grouping in select from DateTime column in SQL Server       How can I get only the Date part of a DateTime   I m searching for something like year   function but for the whole date

User · Answer

This may also help   SELECT convert varchar  getdate    100  -- mon dd yyyy hh mmAM  or PM                                          -- Oct  2 2008 11 01AM SELECT convert varchar  getdate    101  -- mm dd yyyy - 10 02 2008                   SELECT convert varchar  getdate    102  -- yyyy mm dd     2008 10 02            SELECT convert varchar  getdate    103  -- dd mm yyyy SELECT convert varchar  getdate    104  -- dd mm yyyy SELECT convert varchar  getdate    105  -- dd-mm-yyyy SELECT convert varchar  getdate    106  -- dd mon yyyy SELECT convert varchar  getdate    107  -- mon dd  yyyy SELECT convert varchar  getdate    108  --  hh mm ss SELECT convert varchar  getdate    109  -- mon dd yyyy hh mm ss mmmAM  or PM                                          -- Oct  2 2008 11 02 44 013AM    SELECT convert varchar  getdate    110  -- mm-dd-yyyy SELECT convert varchar  getdate    111  -- yyyy mm dd SELECT convert varchar  getdate    112  -- yyyymmdd SELECT convert varchar  getdate    113  -- dd mon yyyy hh mm ss mmm                                         --  02 Oct 2008 11 02 07 577      SELECT convert varchar  getdate    114  -- hh mm ss mmm 24h  SELECT convert varchar  getdate    120  -- yyyy-mm-dd hh mm ss 24h  SELECT convert varchar  getdate    121  --  yyyy-mm-dd hh mm ss mmm SELECT convert varchar  getdate    126  -- yyyy-mm-ddThh mm ss mmm                                         --  2008-10-02T10 52 47 513 -- SQL create different date styles with t-sql string functions SELECT replace convert varchar  getdate    111             -- yyyy mm dd SELECT convert varchar 7   getdate    126                  -- yyyy-mm SELECT right convert varchar  getdate    106   8           -- mon yyyy   The Source

User · Answer

The solution you want is the one proposed here   https   stackoverflow com a 542802 50776  Basically  you do this   cast floor cast  dateVariable as float   as datetime    There is a function definition in the link which will allow you to consolidate the functionality and call it anywhere  instead of having to remember it  if you wish

User · Answer

This may not be as slick as a one-liner  but I use it to perform date manipulation mainly for reports   DECLARE  Date datetime SET  Date   GETDATE    -- Set all time components to zero SET  Date   DATEADD ms  -DATEPART ms   Date    Date  -- milliseconds   0 SET  Date   DATEADD ss  -DATEPART ss   Date    Date  -- seconds   0 SET  Date   DATEADD mi  -DATEPART mi   Date    Date  -- minutes   0 SET  Date   DATEADD hh  -DATEPART hh   Date    Date  -- hours   0  -- Extra manipulation for month and year SET  Date   DATEADD dd  -DATEPART dd   Date    1   Date  -- day   1 SET  Date   DATEADD mm  -DATEPART mm   Date    1   Date  -- month   1   I use this to get hourly  daily  monthly  and yearly dates used for reporting and performance indicators  etc

User · Answer

We can use this method   CONVERT VARCHAR 10   GETDATE    120    Last parameter changes the format to only to get time or date in specific formats

User · Answer

Another nifty way is   DATEADD dd  0  DATEDIFF dd  0   YourDate      Which gets the number of days from DAY 0 to YourDate and the adds it to DAY 0 to set the baseline again  This method  or  derivatives  hereof  can be used for a bunch of other date manipulation   Edit - other date calculations   First Day of Month   DATEADD mm  DATEDIFF mm  0  getdate     0    First Day of the Year   DATEADD yy  DATEDIFF yy  0  getdate     0    First Day of the Quarter   DATEADD qq  DATEDIFF qq  0  getdate     0    Last Day of Prior Month   DATEADD ms  -3  DATEADD mm  DATEDIFF mm  0  getdate     0     Last Day of Current Month   DATEADD ms  -3  DATEADD mm  DATEDIFF m  0  getdate      1  0     Last Day of Current Year   DATEADD ms  -3  DATEADD yy  DATEDIFF yy  0  getdate      1  0     First Monday of the Month   DATEADD wk  DATEDIFF wk  0  DATEADD dd  6 - DATEPART day  getdate     getdate      0            Edit  True  Joe  it does not add it to DAY 0  it adds 0  days  to the number of days which basically just converts it back to a datetime

[sql-server] Get only the Date part of DateTime in mssql

Examples related to sql-server

Examples related to datetime