TSQL strip date from datetime
Try the TimeOnly function from Essential SQL Server Date, Time, and DateTime Functions:
create function TimeOnly(@DateTime DateTime)
returns datetime
as
begin
return dateadd(day, -datediff(day, 0, @datetime), @datetime)
end
go
Or simply cast the DateTime as Time in SQL Server 2008:
declare @time as time
set @time = cast(dateTimeVal as time)
Best approach to remove time part of datetime in SQL Server
Strictly, method a
is the least resource intensive:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time?
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
- varchar is subject to language/dateformat issues
Example: Why is my CASE expression non-deterministic? - float relies on internal storage
- it extends to work out first day of month, tomorrow, etc by changing "0" base
Edit, Oct 2011
For SQL Server 2008+, you can CAST to date
i.e. CAST(getdate() AS date)
. Or just use date
datatype so no time
to remove.
Edit, Jan 2012
A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server
Edit, May 2012
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here Common SQL Programming Mistakes
Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...
Edit, Sep 2018, for datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
How do I strip the date off of a datetime string in SQL SSIS?
I would just do a cast to DT_DBTIME
type (using Derived Column transform, or Convert type transform). DT_DBTIME
contains just (hours, minutes, seconds) part of the date/time, so you'll get rid of the date part.
How to return only the Date from a SQL Server DateTime datatype
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<->datetime conversions required
- No need to think about locale
SQL Server remove milliseconds from datetime
You just have to figure out the millisecond part of the date and subtract it out before comparison, like this:
select *
from table
where DATEADD(ms, -DATEPART(ms, date), date) > '2010-07-20 03:21:52'
Extract date from datetime column - SQL Server Compact
SQL Server Compact has no date type.
If you don't want to see the time, convert the datetime value to a string:
SELECT CONVERT(nvarchar(10), GETDATE(), 120)
(This has been tested and actually works against SQL Server Compact)
Convert a SQL Server datetime to a shorter date format
Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.
e.g.
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format
How to convert datetime to date only (with time set to 00:00:00.000)
For SQL Server 2005 and below:
CONVERT(varchar(8), @ParamDate, 112) -- Supported way
CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME) -- Unsupported way
For SQL Server 2008 and above:
CAST(@ParamDate AS DATE)
Time part of a DateTime Field in SQL
In SQL Server if you need only the hh:mi
, you can use:
DECLARE @datetime datetime
SELECT @datetime = GETDATE()
SELECT RIGHT('0'+CAST(DATEPART(hour, @datetime) as varchar(2)),2) + ':' +
RIGHT('0'+CAST(DATEPART(minute, @datetime)as varchar(2)),2)
Remove time from DateTime sql server 2005
SELECT CONVERT(VARCHAR(10),GETDATE(),105)
Related Topics
What Is the Affect of Convert() on Index While Searching
Simple Recursive Query in Oracle
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
How to Join Two Tables Together with Same Number of Rows by Their Order
How to Calculate Moving Sum with Reset Based on Condition in Teradata SQL
How to Select a Column in SQL Server with a Special Character in the Column Name
Support for JSON in Oracle 11G
Orderby in SQL Server to Put Positive Values Before Negative Values
Update Oracle Table with Values from CSV File
How to Force Nolock Hint for SQL Server Logins
Generating Xml File from SQL Server 2008
SQL Server Query for Many to Many Relationship
Transact-Sql: How to Tokenize a String
Select Columns from One Table Based on the Column Names from Another Table