[sql] How to select date without time in SQL

When I select date in SQL it is returned as 2011-02-25 21:17:33.933. But I need only the Date part, that is 2011-02-25. How can I do this?

This question is related to sql sql-server sql-server-2005

The answer is


Use CAST(GETDATE() as date) that worked for me, simple.


The fastest is datediff, e.g.

select dateadd(d, datediff(d,0, [datecolumn]), 0), other..
from tbl

But if you only need to use the value, then you can skip the dateadd, e.g.

select ...
WHERE somedate <= datediff(d, 0, getdate())

where the expression datediff(d, 0, getdate()) is sufficient to return today's date without time portion.


I would use DATEFROMPARTS function. It is quite easy and you don't need casting. As an example this query :

Select  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())) as myNewDate

will return

2021-01-21

The good part you can also create you own date, for example you want first day of a month as a date, than you can just use like below:

Select  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) as myNewDate

The result will be:

2021-01-01


First Convert the date to float (which displays the numeric), then ROUND the numeric to 0 decimal points, then convert that to datetime.

convert(datetime,round(convert(float,orderdate,101),0) ,101)


In PLSQL you can use

to_char(SYSDATE,'dd/mm/yyyy')

For 2008 older version :

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)


If you want to return a date type as just a date use

CONVERT(date, SYSDATETIME())

or

SELECT CONVERT(date,SYSDATETIME()) 

or

DECLARE @DateOnly Datetime
SET @DateOnly=CONVERT(date,SYSDATETIME())

In case if you need the time to be zeros like 2018-01-17 00:00:00.000:

SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()), 121)


You can try this one too.

SELECT CONVERT(DATE, GETDATE(), 120)

For SQL Server 2008:

Convert(date, getdate())  

Please refer to https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql


Use is simple:

convert(date, Btch_Time)

Example below:

Table:

Efft_d       Loan_I  Loan_Purp_Type_C   Orig_LTV    Curr_LTV    Schd_LTV    Un_drwn_Bal_a      Btch_Time            Strm_I  Btch_Ins_I
2014-05-31  200312500   HL03             NULL         1.0000    1.0000         1.0000      2014-06-17 11:10:57.330  1005    24851e0a-53983699-14b4-69109


Select * from helios.dbo.CBA_SRD_Loan where Loan_I in ('200312500') and convert(date, Btch_Time) = '2014-06-17'

select DATE(field) from table;

field value: 2020-12-15 12:19:00

select value: 2020-12-15


Its too late but following worked for me well

declare @vCurrentDate date=getutcdate()

select @vCurrentDate

When data type is date, hours would be truncated


Convert it back to datetime after converting to date in order to keep same datatime if needed

select Convert(datetime, Convert(date, getdate())  )

you can use like this

SELECT Convert(varchar(10), GETDATE(),120) 

It's a bit late, but use the ODBC "curdate" function (angle brackes 'fn' is the ODBC function escape sequence).

SELECT {fn curdate()} 

Output: 2013-02-01


Try this.

SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?