SQL Time stored as string and want to manipulate it simply

RMS Oceanic

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.

Baron Dirigible

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

RMS Oceanic

ADDTIME is not recognised by my SQL Program. I shoudl explain I'm on Microsoft SQL 2012.

Baron Dirigible

I don't have access to anything to test this on, but:

Update test set dateRow=DATEADD(hh, 1, dateRow)

should work?

RMS Oceanic

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?

RMS Oceanic

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.

Calixtus

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