Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
The best approach would be to use a more appropriate datatype such as datetime. Is that an option? –  Martin Smith Jun 6 '12 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. –  dezso Jun 6 '12 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. –  Petter Brodin Jun 6 '12 at 11:06
add comment

2 Answers 2

up vote 4 down vote accepted

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)
share|improve this answer
add comment

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.

share|improve this answer
1  
Computed columns need to be deterministic to be indexed (among other things) and/or PERSISTED. Adding style parameter 120 to the CONVERT expression makes it deterministic: dt AS CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'), 120) –  Paul White Jun 8 '12 at 4:14
    
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 –  Aaron Bertrand Jun 8 '12 at 12:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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