CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldatetime
ghost_king
ghost_king

Reputation: 930

Time part of a DateTime Field in SQL

How would I be able to extract the time part of a DateTime field in SQL? For my project I have to return data that has a timestamp of 5pm of a DateTime field no matter what the date is

Upvotes: 62

Views: 267486

Answers (13)

Kanchan Dey
Kanchan Dey

Reputation: 71

declare @datetime as datetime
set @datetime = getdate()
select cast(cast(@datetime as time) as varchar(8))

Upvotes: 1

milad bahari javan
milad bahari javan

Reputation: 416

For year:

SELECT DATEPART(YEAR, '2021-03-21' );

For hour:

SELECT DATEPART(HOUR, '2021-03-21 08:50:30' );

Upvotes: 0

AjV Jsy
AjV Jsy

Reputation: 6095

Note that from MS SQL 2012 onwards you can use FORMAT(value,'format')

e.g. WHERE FORMAT(YourDatetime,'HH:mm') = '17:00'

Upvotes: 6

Glen Richmond
Glen Richmond

Reputation: 61

select cast(getdate() as time(0))

returns for example :- 15:19:43

replace getdate() with the date time you want to extract just time from!

Upvotes: 2

Reza Paidar
Reza Paidar

Reputation: 883

you can use CONVERT(TIME,GETDATE()) in this case:

INSERT INTO infoTbl
(itDate, itTime)
VALUES (GETDATE(),CONVERT(TIME,GETDATE()))

or if you want print it or return that time use like this:

DECLARE @dt TIME
SET @dt = CONVERT(TIME,GETDATE())
PRINT @dt

Upvotes: 2

p.ajay
p.ajay

Reputation: 21

SELECT DISTINCT   
                 CONVERT(VARCHAR(17), A.SOURCE_DEPARTURE_TIME, 108)  
FROM  
      CONSOLIDATED_LIST AS A  
WHERE   
      CONVERT(VARCHAR(17), A.SOURCE_DEPARTURE_TIME, 108) BETWEEN '15:00:00' AND '15:45:00'

Upvotes: 1

András Ottó
András Ottó

Reputation: 7695

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)

Upvotes: 38

Heinzi
Heinzi

Reputation: 172468

I know this is an old question, but since the other answers all

  • return strings (rather than datetimes),
  • rely on the internal representation of dates (conversion to float, int, and back) or
  • require SQL Server 2008 or beyond,

I thought I'd add a "pure" option which only requires datetime operations and works with SQL Server 2005+:

SELECT DATEADD(dd, -DATEDIFF(dd, 0, mydatetime), mydatetime)

This calculates the difference (in whole days) between date zero (1900-01-01) and the given date and then subtracts that number of days from the given date, thereby setting its date component to zero.

Upvotes: 11

RYUX123
RYUX123

Reputation: 301

This should strip away the date part:

select convert(datetime,convert(float, getdate()) - convert(int,getdate())), getdate()

and return a datetime with a default date of 1900-01-01.

Upvotes: 2

John Woo
John Woo

Reputation: 263893

This will return the time-Only

For SQL Server:

SELECT convert(varchar(8), getdate(), 108)

Explanation:

getDate() is giving current date and time.
108 is formatting/giving us the required portion i.e time in this case.
varchar(8) gives us the number of characters from that portion.
Like:
If you wrote varchar(7) there, it will give you 00:00:0
If you wrote varchar(6) there, it will give you 00:00:
If you wrote varchar(15) there, it will still give you 00:00:00 because it is giving output of just time portion. SQLFiddle Demo

For MySQL:

SELECT DATE_FORMAT(NOW(), '%H:%i:%s')

SQLFiddle Demo

Upvotes: 76

Jim
Jim

Reputation: 3508

"For my project, I have to return data that has a timestamp of 5pm of a DateTime field, No matter what the date is."

So I think what you meant was that you needed the date, not the time. You can do something like this to get a date with 5:00 as the time:

SELECT CONVERT(VARCHAR(10), GetDate(), 110) + ' 05:00:00'

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74365

Try this in SQL Server 2008:

select *
from some_table t
where convert(time,t.some_datetime_column) = '5pm'

If you want take a random datetime value and adjust it so the time component is 5pm, then in SQL Server 2008 there are a number of ways. First you need start-of-day (e.g., 2011-09-30 00:00:00.000).

  • One technique that works for all versions of Microsoft SQL Server as well as all versions of Sybase is to use convert/3 to convert the datetime value to a varchar that lacks a time component and then back into a datetime value:

    select convert(datetime,convert(varchar,current_timestamp,112),112)

The above gives you start-of-day for the current day.

  • In SQL Server 2008, though, you can say something like this:

    select start_of_day =               t.some_datetime_column
                    - convert(time, t.some_datetime_column ) ,
from some_table t

    which is likely faster.

Once you have start-of-day, getting to 5pm is easy. Just add 17 hours to your start-of-day value:

select five_pm = dateadd(hour,17, t.some_datetime_column
                   - convert(time,t.some_datetime_column)
                   )
from some_table t

Upvotes: 8

Taryn
Taryn

Reputation: 247860

If you want only the hour of your datetime, then you can use DATEPART() - SQL Server:

declare @dt datetime
set @dt = '2012-09-10 08:25:53'

select datepart(hour, @dt) -- returns 8

In SQL Server 2008+ you can CAST() as time:

declare @dt datetime
set @dt = '2012-09-10 08:25:53'

select CAST(@dt as time) -- returns 08:25:53

Upvotes: 34

Related Questions