How to select date without time in SQL
SqlSql ServerSql Server-2005Sql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
For SQL Server 2008:
Convert(date, getdate())
Please refer to https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql
Solution 2 - Sql
I guess he wants a string.
select convert(varchar(10), '2011-02-25 21:17:33.933', 120)
120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.
Solution 3 - Sql
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.
Solution 4 - Sql
Using CAST(GETDATE() As Date) worked for me
Solution 5 - Sql
CAST(
FLOOR(
CAST( GETDATE() AS FLOAT )
)
AS DATETIME
)
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm
Solution 6 - Sql
For 2008 older version :
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Solution 7 - Sql
you can use like this
SELECT Convert(varchar(10), GETDATE(),120)
Solution 8 - Sql
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)
Solution 9 - Sql
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
Solution 10 - Sql
You can try this one too.
SELECT CONVERT(DATE, GETDATE(), 120)
Solution 11 - Sql
Its too late but following worked for me well
declare @vCurrentDate date=getutcdate()
select @vCurrentDate
When data type is date, hours would be truncated
Solution 12 - Sql
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
Solution 13 - Sql
Convert it back to datetime after converting to date in order to keep same datatime if needed
select Convert(datetime, Convert(date, getdate()) )
Solution 14 - 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'
Solution 15 - Sql
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())
Solution 16 - Sql
select DATE(field) from table;
field value: 2020-12-15 12:19:00
select value: 2020-12-15
Solution 17 - Sql
In PLSQL you can use
to_char(SYSDATE,'dd/mm/yyyy')
Solution 18 - Sql
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)
Solution 19 - Sql
Try this.
SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))