?
Solved

SQL query syntax help on time comparison from text string

Posted on 2011-10-06
5
Medium Priority
?
275 Views
Last Modified: 2012-05-12
Experts,

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:
11:43:20
11:43:19
11:43:18

This should NOT be selected:
11:43:17
11:43:21

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

Thanks in advance!
sew
0
Comment
Question by:sungenwang
5 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 total points
ID: 36926184
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")
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 500 total points
ID: 36926325
sql version to convert it to datetime & add 2 minutes

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

2011-10-06 11:45:20.000
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36926349
and you can use

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

to find the interval
0
 
LVL 3

Accepted Solution

by:
kinsey earned 1000 total points
ID: 36927713
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)
+convert(datetime,substring(DSTRING,9,2)+':'
+substring(DSTRING,11,2)+':'
+substring(DSTRING,13,2) ,8) As Dt
FROM Table1) as Ex1
where datediff(s,convert(datetime,substring(DSTRING,1,8),112)
+convert(datetime,substring(DSTRING,9,2)+':'
+substring(DSTRING,11,2)+':'
+substring(DSTRING,13,2) ,8) ,'2011/10/6 11:43:20') between 0 and 2
0
 
LVL 14

Author Closing Comment

by:sungenwang
ID: 36932086
Thanks to all! Although not exactly what I was looking for but they gave me enough insights to solve my query problem!
sew
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

830 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