Time Part of a Datetime Field in SQL

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)

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

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 - add DATE part of DATETIME to TIME part of DATETIME

You can just use DATEADD and DATEDIFF, assuming that the time column's date portion is always 30/12/1899:

declare @t table (DateColumn datetime,TimeColumn datetime)
insert into @t(DateColumn,TimeColumn) values
('2016-05-09T00:00:00.000','1899-12-30T12:26:00.000')

select DATEADD(millisecond,DATEDIFF(millisecond,'18991230',TimeColumn),DateColumn)
from @t

Result:

-----------------------
2016-05-09 12:26:00.000

select query based on time part of the datetime field

 select t_datetime, temp_c from temperatures
 where t_datetime >= '2010-01-01 00:00:00'
 and t_datetime <= '2011-12-01 18:00:00'
 and LTRIM(RIGHT(CONVERT(VARCHAR(20), t_datetime, 100), 7))='6:00PM'
 order by t_datetime

How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

1. If you have control over the database schema, and you don't need the
   time data, take it out.

2. or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...

Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server...

Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'

How to only keep the time portion of a datetime value (SQL Server)?

Declare @D datetime = GetDate()      -- Data Type could be Time as well

Select CONVERT(varchar(8), @D, 108)  -- for HH:MM:SS  11:27:26
Select CONVERT(varchar(5), @D, 108)  -- for HH:MM     11:27

Failed to mention, if 2012+ you could also use Format()

Declare @D DateTime = '2016-10-22 13:30:25'

Select Format(@D,'HH:mm')         -- 13:30
Select Format(@D,'hh:mm:ss tt')   -- 01:30:25 PM

Compare time portion of datetime values in sql

If you use SQL Server 2008 or newer, you can cast to TIME datatype to compare the time portion.

SELECT Model 
FROM Sales 
WHERE 
  CAST(@saledate AS time) BETWEEN CAST(WorkStart AS time) AND CAST(WorkEnd AS time) 
  AND Ctrl = @key

If you do that often, it would be more performant to have your times stored to database columns as TIME, not DATETIME.

MySQL: Get the datetime field with time part reset to midnight (00:00:00)?

Convert to date to truncate the time part, then cast to datetime:

select
    PurchaseDate,
    cast(date(PurchaseDate) as datetime) as StartOfDay
from mytable

See live demo.

Update only time from my Datetime field in sql

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME)) 

Or this

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))

---

Related Topics