Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

Question

I'm working with a table where the date and time of an event is stored as a char in the format YYYMMDDHHmmSS or '20120606122012'. I need to compare this to the current timestamp to see if it's been more than 20 minutes since the event happened, but that seems to be more difficult than I anticipated.

Is it possible to do this, or if not: is there a different approach that works?

Answer 1

Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.

Something like this:

declare @S varchar(50)
set @S = '20120606122012'

select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)

Answer 2

You could also add this as a computed column or make it part of a view, so that you don't have to perform the calculation over and over in every query.

As a computed column:

ALTER TABLE dbo.whatever ADD dt
AS CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'), 120);

Since the calculation is deterministic, it can be persisted and/or indexed.

Using a view:

CREATE VIEW dbo.view_whatever
AS
    SELECT /* other columns, */
      dt = CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'))
    FROM dbo.whatever;

Then you can simply say:

WHERE dt < DATEADD(MINUTE, -20, CURRENT_TIMESTAMP) 

Though I have to agree with the others - you need to fix the model. There is no advantage whatsoever to storing this as a string, except that your users don't get pesky error messages when they enter non-date junk like whosawatsa or even 20120230000000.