Time part of a DateTime Field in SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Note that from MS SQL 2012 onwards you can use FORMAT value  format    e g  WHERE FORMAT YourDatetime  HH mm      17 00

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Time part of a DateTime Field in SQL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to datetime