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?

  • 2
    The best approach would be to use a more appropriate datatype such as datetime. Is that an option? Commented Jun 6, 2012 at 10:45
  • First convert your string to datetime and then you can use datediff to check the difference between the time of the event and the current timestamp. But the best would be to follow @MartinSmith 's advice. Commented Jun 6, 2012 at 10:46
  • I agree that would be the best way to do it, but unfortunately the guy who designed this wasn't in the habit of doing things the best way. We're working on cleaning up the data model, but this needs to be done before we can finish that work. Commented Jun 6, 2012 at 11:06

2 Answers 2


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)

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
    SELECT /* other columns, */
      dt = CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'))
    FROM dbo.whatever;

Then you can simply say:


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.

  • Thanks Paul, will correct. Certain I was able to add an index without the style parameter but also know that I've had to do it that way in the past Commented Jun 8, 2012 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.