How to return only the Date from a SQL Server DateTime datatype

Question

SELECT GETDATE     Returns  2008-09-22 15 24 13 790  I want that date part without the time part  2008-09-22 00 00 00 000  How can I get that

User · Answer

As there has been many changes since this question had answers, I wanted to provide a new way to get the requested result. There are two ways to parse DATETIME data. First, to get the date as this question asks:

DATEVALUE([TableColumnName])

Second, to get the time from the value:

TIMEVALUE([TableColumnName])

Example:

Table: Customers

Column: CreationDate as DateTime

[Customers].[CreationDate]: 2/7/2020 09:50:00

DATEVALUE([Customers].[CreationDate]) '--> Output: 2/7/2020
TIMEVALUE([Customers].[CreationDate]) '--> Output: 09:50:00

I hope that this helps as I was searching for a while and found many answers as seen in this question and none of those worked. IE CAST and CONVERT.

Happy Coding!

User · Answer

Simply you can do this way   SELECT CONVERT date  getdate    SELECT DATEADD dd  0  DATEDIFF dd  0   your date   SELECT DATEADD dd  0  DATEDIFF dd  0  GETDATE       Outputs as   2008-09-22 00 00 00 000   Or simply do like this   SELECT CONVERT  DATE  GETDATE     Date Part Only    Result   Date Part Only -------------- 2013-07-14

User · Answer

On SQL Server 2008 and higher  you should CONVERT to date   SELECT CONVERT date  getdate      On older versions  you can do the following   SELECT DATEADD dd  0  DATEDIFF dd  0   your date     for example  SELECT DATEADD dd  0  DATEDIFF dd  0  GETDATE       gives me     2008-09-22 00 00 00 000   Pros     No varchar lt - datetime conversions required No need to think about locale     As suggested by Michael  Use this variant  SELECT DATEADD dd  DATEDIFF dd  0  getdate     0   select getdate    SELECT DATEADD hh  DATEDIFF hh  0  getdate     0  SELECT DATEADD hh  0  DATEDIFF hh  0  getdate      SELECT DATEADD dd  DATEDIFF dd  0  getdate     0  SELECT DATEADD dd  0  DATEDIFF dd  0  getdate      SELECT DATEADD mm  DATEDIFF mm  0  getdate     0  SELECT DATEADD mm  0  DATEDIFF mm  0  getdate      SELECT DATEADD yy  DATEDIFF yy  0  getdate     0  SELECT DATEADD yy  0  DATEDIFF yy  0  getdate       Output   2019-04-19 08 09 35 557  2019-04-19 08 00 00 000 4763-02-17 00 00 00 000  2019-04-19 00 00 00 000 2019-04-19 00 00 00 000  2019-04-01 00 00 00 000 1903-12-03 00 00 00 000  2019-01-01 00 00 00 000 1900-04-30 00 00 00 000

User · Answer

Even using the ancient MSSQL Server 7 0  the code here  courtesy of this link  allowed me to get whatever date format I was looking for at the time     PRINT  1  Date time in format MON DD YYYY HH MI AM  OR PM       CONVERT CHAR 19  GETDATE      PRINT  2  Date time in format MM-DD-YY      CONVERT CHAR 8  GETDATE   10    PRINT  3  Date time in format MM-DD-YYYY      CONVERT CHAR 10  GETDATE   110   PRINT  4  Date time in format DD MON YYYY      CONVERT CHAR 11  GETDATE   106  PRINT  5  Date time in format DD MON YY      CONVERT CHAR 9  GETDATE   6   PRINT  6  Date time in format DD MON YYYY HH MM SS MMM 24H       CONVERT CHAR 24  GETDATE   113    It produced this output     1  Date time in format MON DD YYYY HH MI AM  OR PM   Feb 27 2015  1 14PM 2  Date time in format MM-DD-YY  02-27-15 3  Date time in format MM-DD-YYYY  02-27-2015 4  Date time in format DD MON YYYY  27 Feb 2015 5  Date time in format DD MON YY  27 Feb 15 6  Date time in format DD MON YYYY HH MM SS MMM 24H   27 Feb 2015 13 14 46 630

User · Answer

I favor the following which wasn t mentioned   DATEFROMPARTS DATEPART yyyy   mydatetime   DATEPART mm   mydatetime   DATEPART dd   mydatetime     It also doesn t care about local or do a double convert -- although each  datepart  probably does math   So it may be a little slower than the datediff method  but to me it is much more clear   Especially when I want to group by just the year and month  set the day to 1

User · Answer

IF you want to use CONVERT and get the same output as in the original question posed  that is  yyyy-mm-dd then use CONVERT varchar 10   SourceDate as dateTime  121  same code as the previous couple answers  but the code to convert to yyyy-mm-dd with dashes is 121   If I can get on my soapbox for a second  this kind of formatting doesn t belong in the data tier  and that s why it wasn t possible without silly high-overhead  tricks  until SQL Server 2008 when actual datepart data types are introduced  Making such conversions in the data tier is a huge waste of overhead on your DBMS  but more importantly  the second you do something like this  you have basically created in-memory orphaned data that I assume you will then return to a program  You can t put it back in to another 3NF  column or compare it to anything typed without reverting  so all you ve done is introduced points of failure and removed relational reference    You should ALWAYS go ahead and return your dateTime data type to the calling program and in the PRESENTATION tier  make whatever adjustments are necessary  As soon as you go converting things before returning them to the caller  you are removing all hope of referential integrity from the application  This would prevent an UPDATE or DELETE operation  again  unless you do some sort of manual reversion  which again is exposing your data to human code gremlin error when there is no need

User · Answer

In this case  date only  you we are gonna run this query   SELECT CONVERT VARCHAR 10   getdate    111

User · Answer

DECLARE  yourdate DATETIME    11 1 2014 12 25pm      SELECT CONVERT DATE   yourdate

User · Answer

you can use like below for different different type of output for date only   SELECT CONVERT datetime  CONVERT varchar  GETDATE    103   -----dd mm yyyy SELECT CONVERT datetime  CONVERT varchar  GETDATE    101  ------mm dd yyyy SELECT CONVERT datetime  CONVERT varchar  GETDATE    102

User · Answer

If using SQL 2008 and above   select cast getdate   as date

User · Answer

You can simply use the code below to get only the date part and avoid the time part in SQL   SELECT SYSDATE TODAY FROM DUAL

User · Answer

To obtain the result indicated  I use the following command   SELECT CONVERT DATETIME CONVERT DATE GETDATE       I holpe it is useful

User · Answer

SELECT CONVERT VARCHAR DATEADD DAY -1 GETDATE    103  --21 09 2011  SELECT CONVERT VARCHAR DATEADD DAY -1 GETDATE    101  --09 21 2011  SELECT CONVERT VARCHAR DATEADD DAY -1 GETDATE    111  --2011 09 21  SELECT CONVERT VARCHAR DATEADD DAY -1 GETDATE    107  --Sep 21  2011

User · Answer

I know this is old  but I do not see where anyone stated it this way  From what I can tell  this is ANSI standard   SELECT CAST CURRENT TIMESTAMP AS DATE    It would be good if Microsoft could also support the ANSI standard CURRENT DATE variable

User · Answer

Convert nvarchar 10   getdate    101  --- gt   5 12 14   Convert nvarchar 12   getdate    101  --- gt   5 12 2014

User · Answer

select convert getdate   as date   select CONVERT datetime CONVERT date  getdate

User · Answer

SQLServer 2008 now has a  date  data type which contains only a date with no time component  Anyone using SQLServer 2008 and beyond can do the following   SELECT CONVERT date  GETDATE

User · Answer

You can use following for date part and formatting the date   DATENAME    Returns a character string that represents the specified datepart of the specified date  DATEADD    The DATEPART   function is used to return a single part of a date time  such as year  month  day  hour  minute  etc   DATEPART   Returns an integer that represents the specified datepart of the specified date   CONVERT       The CONVERT   function is a general function that converts an expression of one data type to another  The  CONVERT   function can be used to display date time data in different formats

User · Answer

Wow  let me count the ways you can do this   no pun intended   In order to get the results you want in this format specifically   2008-09-22  Here are a few options    SELECT CAST GETDATE   AS DATE  AS  Date1  SELECT Date2    CONVERT DATE  GETDATE    SELECT CONVERT DATE  GETDATE    AS  Date3  SELECT CONVERT CHAR 10   GETDATE    121  AS  Date4  SELECT CONVERT CHAR 10   GETDATE    126  AS  Date5  SELECT CONVERT CHAR 10   GETDATE    127  AS  Date6    So  I would suggest picking one you are comfortable with and using that method across the board in all your tables    All these options return the date in the exact same format  Why does SQL Server have such redundancy   I have no idea  but they do  Maybe somebody smarter than me can answer that question    Hope this helps someone

User · Answer

My Style        select Convert smalldatetime Convert int Convert float getdate

User · Answer

My common approach to get date without the time part        SELECT CONVERT VARCHAR MAX  GETDATE   103    SELECT CAST GETDATE   AS DATE

User · Answer

select cast createddate as date  as derivedate from table    createdate is your datetime column   this works for sqlserver

User · Answer

SELECT   FROM tablename WHERE CAST   my date time var  AS DATE    8 5 2015

User · Answer

If you are using SQL Server 2012 or above versions   Use Format   function   There are already multiple answers and formatting types for SQL server   But most of the methods are somewhat ambiguous and it would be difficult for you to remember the numbers for format type or functions with respect to Specific Date Format  That s why in next versions of SQL server there is better option   FORMAT   value  format    culture       Culture option is very useful  as you can specify date as per your viewers   You have to remember d  for small patterns  and D  for long patterns    1  d  - Short date pattern   2009-06-15T13 45 30 - gt  6 15 2009  en-US  2009-06-15T13 45 30 - gt  15 06 2009  fr-FR  2009-06-15T13 45 30 - gt  2009 06 15  ja-JP    2  D  - Long date pattern   2009-06-15T13 45 30 - gt  Monday  June 15  2009  en-US  2009-06-15T13 45 30 - gt  15      2009     ru-RU  2009-06-15T13 45 30 - gt  Montag  15  Juni 2009  de-DE    More examples in query   DECLARE  d DATETIME    10 01 2011   SELECT FORMAT    d   d    en-US    AS  US English Result         FORMAT    d   d    en-gb    AS  Great Britain English Result         FORMAT    d   d    de-de    AS  German Result         FORMAT    d   d    zh-cn    AS  Simplified Chinese  PRC  Result     SELECT FORMAT    d   D    en-US    AS  US English Result         FORMAT    d   D    en-gb    AS  Great Britain English Result         FORMAT    d   D    de-de    AS  German Result         FORMAT    d   D    zh-cn    AS  Chinese  Simplified PRC  Result    US English Result Great Britain English Result  German Result Simplified Chinese  PRC  Result ----------------  ----------------------------- ------------- ------------------------------------- 10 1 2011         01 10 2011                    01 10 2011    2011 10 1  US English Result            Great Britain English Result  German Result                    Chinese  Simplified PRC  Result ---------------------------- ----------------------------- -----------------------------  --------------------------------------- Saturday  October 01  2011   01 October 2011               Samstag  1  Oktober 2011        2011 10 1    If you want more formats  you can go to    Standard Date and Time Format Strings Custom Date and Time Format Strings

User · Answer

Try this   SELECT CONVERT VARCHAR 10  GETDATE   111    The above statement converts your current format to YYYY MM DD  please refer to this link to choose your preferable format

User · Answer

Starting from SQL SERVER 2012  you can do this   SELECT FORMAT GETDATE     yyyy-MM-dd 00 00 00 000

User · Answer

You can use the CONVERT function to return only the date  See the link s  below   Date and Time Manipulation in SQL Server 2000   CAST and CONVERT  The syntax for using the convert function is    CONVERT   data type     length       expression     style

User · Answer

If you want the date to show 2008-09-22 00 00 00 000  then you can round it using  SELECT CONVERT datetime   ROUND convert float  getdate  - 5  0      This will show the date in the format in the question

User · Answer

DATEADD and DATEDIFF are better than CONVERTing to varchar  Both queries have the same execution plan  but execution plans are primarly about data access strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces  If both queries are run against a table with millions of rows  the CPU time using DateDiff can be close to 1 3rd of the Convert CPU time   To see execution plans for queries   set showplan text on GO    Both DATEADD and DATEDIFF will execute a CONVERT IMPLICIT   Although the CONVERT solution is simpler and easier to read for some  it is slower  There is no need to cast back to datetime  this is implicitly done by the server   There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to datetime     SELECT CONVERT varchar  MyDate  101  FROM DatesTable     --Compute Scalar DEFINE   Expr1004  CONVERT varchar 30   TEST   dbo   DatesTable   MyDate  101            --Table Scan OBJECT   TEST   dbo   DatesTable        SELECT DATEADD dd  0  DATEDIFF dd  0  MyDate   FROM DatesTable     --Compute Scalar DEFINE   Expr1004  dateadd day  0  CONVERT IMPLICIT datetime datediff day  1900-01-01 00 00 00 000  CONVERT IMPLICIT datetime  TEST   dbo   DatesTable   MyDate  0   0             --Table Scan OBJECT   TEST   dbo   DatesTable      Using FLOOR   as  digi suggested has performance closer to DateDiff  but is not recommended as casting the datetime data type to float and back does not always yield the original value   Remember guys  Don t believe anyone  Look at the performance statistics  and test it yourself   Be careful when you re testing your results  Selecting many rows to the client will hide the performance difference becauses it takes longer to send the rows over the network than it does to perform the calculations  So make sure that the work for all the rows is done by the server but there is no rowset sent to the client   There seems to be confusion for some people about when cache optimization affects queries  Running two queries in the same batch or in separate batches has no effect on caching  So you can either expire the cache manually or simply run the queries back and forth multiple times  Any optimization for query  2 would also affect any subsequent queries  so throw out execution  1 if you like   Here is full test script and performance results that prove DateDiff is substantially faster than converting to varchar

User · Answer

On SQL Server 2000  CAST        STR  YEAR  GETDATE                   STR  MONTH  GETDATE                   STR  DAY  GETDATE         AS DATETIME

User · Answer

Just do   SELECT CAST date variable AS date   or with  with PostgreSQL   SELECT date variable  date

User · Answer

Using FLOOR   - just cut time part   SELECT CAST FLOOR CAST GETDATE   AS FLOAT   AS DATETIME

User · Answer

Date    SELECT CONVERT date  GETDATE    SELECT CAST GETDATE   as date    Time    SELECT CONVERT time   GETDATE     114  SELECT CAST GETDATE   as time

User · Answer

SELECT CONVERT datetime  CONVERT varchar  GETDATE    101

User · Answer

why don t you use  DATE FORMAT  your datetiem column     d- m- Y       EX  select  DATE FORMAT  some datetime column     d- m- Y    from table name  you can change sequence of m d and year by re-arranging   d- m- Y  part

User · Answer

If you are assigning the results to a column or variable  give it the DATE type  and the conversion is implicit   DECLARE  Date DATE   GETDATE       SELECT  Date   -- gt  2017-05-03

User · Answer

Okay  Though I m bit late     Here is the another solution   SELECT CAST FLOOR CAST GETDATE   AS FLOAT   as DATETIME    Result  2008-09-22 00 00 00 000   And if you are using SQL Server 2012 and higher then  you can use FORMAT   function like this -  SELECT FORMAT GETDATE     yyyy-MM-dd

User · Answer

The easiest way would be to use  SELECT DATE GETDATE

User · Answer

SELECT DATEADD DD  DATEDIFF DD  0  GETDATE     0   SELECT DATEADD DAY  0  DATEDIFF DAY 0  GETDATE      SELECT CONVERT DATETIME  CONVERT VARCHAR 10   GETDATE    101     Edit  The first two methods are essentially the same  and out perform the convert to varchar method

User · Answer

Date date amp time field  and DATE FORMAT date amp time   Y- m- d   both returns only date  from date amp time

User · Answer

For return in date format      CAST OrderDate AS date    The above code will work in sql server 2010  It will return like   12 12 2013  For SQL Server 2012 use the below code  CONVERT VARCHAR 10   OrderDate   111

User · Answer

If you need the result as a varchar  you should go through  SELECT CONVERT DATE  GETDATE    --2014-03-26 SELECT CONVERT VARCHAR 10   GETDATE    111  --2014 03 26   which is already mentioned above   If you need result in date and time format  you should use any of the queries below   SELECT CONVERT DATETIME  CONVERT VARCHAR 10   GETDATE    111   AS OnlyDate       2014-03-26 00 00 00 000  SELECT CONVERT DATETIME  CONVERT VARCHAR 10   GETDATE    112   AS OnlyDate       2014-03-26 00 00 00 000  DECLARE   OnlyDate DATETIME SET  OnlyDate   DATEDIFF DD  0  GETDATE    SELECT  OnlyDate AS OnlyDate      2014-03-26 00 00 00 000

User · Answer

I think this would work in your case   CONVERT VARCHAR 10  Person DateOfBirth 111  AS BirthDate   here date is obtained as 1990 09 25

[sql] How to return only the Date from a SQL Server DateTime datatype

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to date

Examples related to datetime