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
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)
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
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()))
Source: Stackoverflow.com