locked
get hr and time separate column in resulet RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my field 1 data
    18:53
    20:31
    18:44
    18:01

    I want hours and minutes seperate like this below

    Field1 Field2
    18 53
    20 31
    18 44
    18 1

    how to do so using sql 2008

    Monday, March 22, 2021 12:38 PM

Answers

  • User475983607 posted

    but i want prefix zero to be add like this below

    Pad zeros.

    SELECT @time,  
    RIGHT('00' + CAST(DATEPART(HOUR, CAST(@time AS time(2))) AS VARCHAR(2)), 2) [Hour], RIGHT('00' + CAST(DATEPART(MINUTE, CAST(@time as time(2))) AS VARCHAR(2)) ,2) [Minute];

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 24, 2021 11:01 AM

All replies

  • User475983607 posted

    What is the column type?  Is it a standard TIME?

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-ver15

    DECLARE @time time(2) = '18:53';
    SELECT @time, DATEPART(HOUR, @time) [Hour],DATEPART(MINUTE, @time) [Minute];

    If the column is a VARCHAR then cast to a time.  

    DECLARE @time VARCHAR(5) = '18:53';
    SELECT @time,  DATEPART(HOUR, CAST(@time AS time(2))) [Hour],DATEPART(MINUTE, CAST(@time as time(2))) [Minute];

    Monday, March 22, 2021 12:53 PM
  • User-807418713 posted

    hello

    if i pass this query

    DECLARE @time VARCHAR(5) = '18:01';
    SELECT @time,  DATEPART(HOUR, CAST(@time AS time(2))) [Hour],DATEPART(MINUTE, CAST(@time as time(2))) [Minute];

    it shows like this

    (No column name) Hour Minute
    18:01 18 1

    but i want prefix zero to be add like this below

    (No column name) Hour Minute
    18:01 18 01

    Wednesday, March 24, 2021 8:13 AM
  • User475983607 posted

    but i want prefix zero to be add like this below

    Pad zeros.

    SELECT @time,  
    RIGHT('00' + CAST(DATEPART(HOUR, CAST(@time AS time(2))) AS VARCHAR(2)), 2) [Hour], RIGHT('00' + CAST(DATEPART(MINUTE, CAST(@time as time(2))) AS VARCHAR(2)) ,2) [Minute];

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 24, 2021 11:01 AM