Avatar of sungenwang
sungenwangFlag for United States of America

asked on 

SQL query syntax help on time comparison from text string

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
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
sungenwang
SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of HainKurt
HainKurt
Flag of Canada image

and you can use

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

to find the interval
ASKER CERTIFIED SOLUTION
Avatar of kinsey
kinsey

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of sungenwang
sungenwang
Flag of United States of America image

ASKER

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo