SQL query syntax help on time comparison from text string

Posted on 2011-10-06
Last Modified: 2012-05-12

What SQL query syntax should I use to retrieve all records where the processed time is within two minutes? There's a twist: my processed time field is a text field in this format YYYYMMDDHHNNSS (e.g. 20111006114320). I need to select all records where the process time is within two minutes of a specific.

For example, if the current process time is 11:43:20 (20111006114320), then records with these time value should be selected:

This should NOT be selected:

The SQL query syntax needs to work in both MS Access and SQL Server

Thanks in advance!
Question by:sungenwang
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    Your question says 2 min but your examples indicate 2 seconds.

    The syntax will vary between Access and SQL Server.

    Access, assuming +/- 2 min:

    Select *
    From sometable
    Where processedtime >= format(datediff("n", -2, now()), "yyyymmddhhnnss") and processedtime <= format(datediff("n",2,now()), "yyyymmddhhnnss")
    LVL 51

    Assisted Solution

    sql version to convert it to datetime & add 2 minutes

    declare @dt varchar(20) = '20111006114320'
    convert(datetime, left(@dt,8))+ convert(time, stuff(stuff(right(@dt,6),3,0,':'), 6,0,':'))

    2011-10-06 11:45:20.000
    LVL 51

    Expert Comment

    and you can use

    abs(datediff(minute, date1, date2)) <= 2

    to find the interval
    LVL 3

    Accepted Solution

    I thnik you are out of luck looking for a single SQL statement that will work for Access and MSSQL
    They have different DateTime formats, different conversion functions, and nither can directly convert your processed time field to a date time.

    From looking at your example I assume you mean 2 seconds

    For access you can convert Now() and DateAdd("s",-2,Now()) to your time format and use them in the where clause.
    For SQL you would use GetDate() and DateAdd(s,-2,Now())

    Or you can convert your data to a standard DateTime:
    For Access
    SELECT Queryd.DSTRING, Queryd.Expr1
    FROM (SELECT Table1.DSTRING, CDate(Mid([DSTRING],5,2) & "-" & Mid([DSTRING],7,2)
    & "-" & Mid([DSTRING],1,4) & " " & Mid([DSTRING],9,2) & ":" & Mid([DSTRING],11,2)
    & ":" & Mid([DSTRING],13,2)) AS Expr2
    , DateDiff("s",[Expr2],"10/6/2011 11:43:20AM",0,0) AS Expr1
    FROM Table1) As Queryd
    WHERE (((Queryd.Expr1)>=0 And (Queryd.Expr1)<=2));

    For MSSQL
    Select * from (
    SELECT DSTRING, convert(datetime,substring(DSTRING,1,8),112)
    +substring(DSTRING,13,2) ,8) As Dt
    FROM Table1) as Ex1
    where datediff(s,convert(datetime,substring(DSTRING,1,8),112)
    +substring(DSTRING,13,2) ,8) ,'2011/10/6 11:43:20') between 0 and 2
    LVL 14

    Author Closing Comment

    Thanks to all! Although not exactly what I was looking for but they gave me enough insights to solve my query problem!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now