Convert time from one format to another

  • i need to convert a time format to another format

    eg. if the time format is 6, how can i convert it to 0600

    please assist

    I learn from the footprints of giants......

  • Assuming the 6 in your example represents an hour you could do this:

    DECLARE @time varchar(10) = 24;

    SELECT MAX(CONCAT(REPLICATE(0,2-LEN(@time)),@time,'00'))

    WHERE @time BETWEEN 0 AND 24;

    This returns the time in military format; if that's not what you're looking for please post back with more details.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks Allan, this throws an error, i passed the column in the table and set that column as the default value for the variable @time ,

    i did this:

    DECLARE @Starttime varchar(10) = 24;

    DECLARE @Endtime varchar (10) =24;

    SET @Starttime = (SELECT StartTime FROM [Inventoryfilltable].[dbo].[InventoryFilltable])

    SET @endtime =(SELECT EndTime FROM [Inventoryfilltable].[dbo].[InventoryFilltable])

    SELECT MAX(CONCAT(REPLICATE(0,2-LEN(@Starttime)),@Starttime,'00')),

    MAX(CONCAT(REPLICATE(0,2-LEN(@Endtime)),@Endtime,'00'))

    FROM [Inventoryfilltable].[dbo].[InventoryFilltable]

    WHERE @starttime BETWEEN 0 AND 24 AND @Endtime BETWEEN 0 AND 24

    it throws an error below:

    Msg 195, Level 15, State 10, Line 26

    'CONCAT' is not a recognized built-in function name.

    pls assist further

    I learn from the footprints of giants......

  • JALLYKAMOZE (9/19/2016)


    Thanks Allan, this throws an error, i passed the column in the table and set that column as the default value for the variable @time ,

    i did this:

    DECLARE @Starttime varchar(10) = 24;

    DECLARE @Endtime varchar (10) =24;

    SET @Starttime = (SELECT StartTime FROM [Inventoryfilltable].[dbo].[InventoryFilltable])

    SET @endtime =(SELECT EndTime FROM [Inventoryfilltable].[dbo].[InventoryFilltable])

    SELECT MAX(CONCAT(REPLICATE(0,2-LEN(@Starttime)),@Starttime,'00')),

    MAX(CONCAT(REPLICATE(0,2-LEN(@Endtime)),@Endtime,'00'))

    FROM [Inventoryfilltable].[dbo].[InventoryFilltable]

    WHERE @starttime BETWEEN 0 AND 24 AND @Endtime BETWEEN 0 AND 24

    it throws an error below:

    Msg 195, Level 15, State 10, Line 26

    'CONCAT' is not a recognized built-in function name.

    pls assist further

    Ah, you posted in the SQL Server 2012 forum, CONCAT is a 2012 feature. You can do this instead:

    DECLARE @time varchar(10) = 1;

    SELECT MAX(REPLICATE(0,2-LEN(@time)) + CAST(@time AS varchar(4)) + '00')

    WHERE @time BETWEEN 0 AND 24;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • JALLYKAMOZE (9/19/2016)


    i need to convert a time format to another format

    eg. if the time format is 6, how can i convert it to 0600

    please assist

    TIME data DOES NOT HAVE A FORMAT. Character strings that represent time have a format, but time data itself does not have a format.

    Except for display purposes, you should ALWAYS use the TIME data type for TIME data. You're running into problems, because you're violating this simple rule.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (9/19/2016)


    JALLYKAMOZE (9/19/2016)


    i need to convert a time format to another format

    eg. if the time format is 6, how can i convert it to 0600

    please assist

    TIME data DOES NOT HAVE A FORMAT. Character strings that represent time have a format, but time data itself does not have a format.

    Except for display purposes, you should ALWAYS use the TIME data type for TIME data. You're running into problems, because you're violating this simple rule.

    Drew

    I think the OP was just looking for hours/minutes in this format: HHMM.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • All display formatting in SQL is done in a presentation layer, never in the database. If you will read the SQL standards, you will find that we use only one format. It is based on ISO 8601 standard (yyyy-mm-dd hh:mm:ss....). why do you think that a single digit can represent a time?:w00t:

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • Alan.B (9/19/2016)


    drew.allen (9/19/2016)


    JALLYKAMOZE (9/19/2016)


    i need to convert a time format to another format

    eg. if the time format is 6, how can i convert it to 0600

    please assist

    TIME data DOES NOT HAVE A FORMAT. Character strings that represent time have a format, but time data itself does not have a format.

    Except for display purposes, you should ALWAYS use the TIME data type for TIME data. You're running into problems, because you're violating this simple rule.

    Drew

    I think the OP was just looking for hours/minutes in this format: HHMM.

    The purpose of these forums is for education. We fail in that mission if we only hand the OP what he wants and ignore obvious problems with their approach.

    We don't know the data types of the table, but they are using two or three data types in dealing with one piece of information. They're using a VARCHAR field and comparing it to an INT, from a field that is most likely TIME, but could also be VARCHAR or DATETIME.

    They also select MAX() of expressions that contain only constants and variables.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Alan.B (9/19/2016)


    Assuming the 6 in your example represents an hour you could do this:

    DECLARE @time varchar(10) = 24;

    SELECT MAX(CONCAT(REPLICATE(0,2-LEN(@time)),@time,'00'))

    WHERE @time BETWEEN 0 AND 24;

    This returns the time in military format; if that's not what you're looking for please post back with more details.

    Amen to that!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There is little problem here. Time actually goes from 00:00:00 Hrs to 23:59:59; there is no 24:00:00. The ISO 8601 model is based on half open intervals. IBM is had the convention that 24:00:00 is converted to 00:00:00 the next day.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • CELKO (9/19/2016)


    All display formatting in SQL is done in a presentation layer, never in the database. If you will read the SQL standards, you will find that we use only one format. It is based on ISO 8601 standard (yyyy-mm-dd hh:mm:ss....). why do you think that a single digit can represent a time?:w00t:

    I agree with this so much. Store the data in the appropriate data type and let formatting be done in the presentation layer. It's absolutely the correct way to do things. All this hopping through hoops to write quite odd T-SQL commands is because of this fundamental violation of standards.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply