Post reply

Converting VARCHAR value to TIME

  • October 1, 2009 at 12:44 pm

    #135540

    Part of our project involves altering a column in our table from BIGINT to VARCHAR(30). The data contained in the column is TIME. The formula below successfully converts the BIGINT data in the column to a TIME value. Now we need a formula to convert the VARCHAR(30) value to a TIME since we are changing the column datatype from BIGINT to VARCHAR(30). Can anyone help tweak the formula below to do the conversion? Your help is appreciated.

    SELECT TOP 100

    COL_X,

    CONVERT(VARCHAR(12),DATEADD(SECOND, COL_TM / 10000000, '19700101 00:00'),114) AS Time

    FROM TABLE_X (NOLOCK)

    WHERE COL_TM IS NOT NULL

  • October 1, 2009 at 3:06 pm

    #1060710

    It's a mistake to do so. You'll be losing the date element forever. Instead of converting the BIGINT to VARCHAR etc, etc, convert it to a DATETIME and enjoy all the benefits of having date and time together.

    If you need to see just the time, do it as a formula, not as stored data. You could even add a computed column to the table so you wouldn't have to mess with it in the future.

    --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)

Viewing 2 posts - 1 through 1 (of 1 total)

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