Penny Arcade Forums› Help / Advice Forum
As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

SQL Time stored as string and want to manipulate it simply

RMS OceanicRMS Oceanic Registered User regular
edited July 2014 in Help / Advice Forum
So in a table I'm working on, there is a column of times stored in string format. So 5pm is stored as '17:00'.

I'm trying to move a lot of data from this table to a second table, but the rub is the time has to be one hour later than it originally was. So '18:00' in this case.

Is there a simple way to select a string '17:00' and have it turn out as '18:00' that would fit into an insert statement? I know that 
SELECT time+'01:00' from table

Doesn't work, that just spits out '17:0001:00'.

Oh and the column I'm inserting it into is configured the same as the original column, and neither can be changed. So any variable type conversion has to be reverted.

RMS Oceanic on

Posts

  • Options
    Baron DirigibleBaron Dirigible Registered User regular
    edited July 2014
    Best I can come up with is: 
    UPDATE `test` SET timeRow = ADDTIME(timeRow,'01:00:00')
    The only problem is that this won't wrap around past midnight, as it seems the TIME datatype is used to handle time differences (i.e. how long a certain task took) and not represent legal times. But that's a problem with the datatype and you can probably work around that somehow.

    [edit: to clarify, this will still work if you're not using the TIME datatype, but I assume that's behind the limitation. It'll work on a string datatype, presumably as long as it's in the format hh:mm

    Baron Dirigible on
  • Options
    RMS OceanicRMS Oceanic Registered User regular
    ADDTIME is not recognised by my SQL Program. I shoudl explain I'm on Microsoft SQL 2012.

  • Options
    Baron DirigibleBaron Dirigible Registered User regular
    I don't have access to anything to test this on, but: 
    Update test set dateRow=DATEADD(hh, 1, dateRow)
    should work?

  • Options
    RMS OceanicRMS Oceanic Registered User regular
    It sort of works! I now get '1900-01-01 18:00:00.000'. What's the simplest way to extract '18:00' back into a string?

  • Options
    RMS OceanicRMS Oceanic Registered User regular
    Ah, figured it out: 
    SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(hh, 1, Time), 20), 12, 5) from table

    Thanks for pointing me in the right direction.

  • Options
    CalixtusCalixtus Registered User regular
    You should also be able to use DATEPART to extract the hour after using DATEADD gives you a DATETIME. It'll probably be faster than recasting to a char.

    http://msdn.microsoft.com/en-us/library/ms174420.aspx

    -This message was deviously brought to you by:
Sign In or Register to comment.
Penny Arcade Forums› Help / Advice Forum