Date ranges in SQL select query

Question

My SQL database performs a backup of all records from an Access database daily, indicating the backup time with the field 'Logtime' (formatted as datetime). The point of my SQL database is to store all versions of my Access database records. For example, if an entry in the Access database is changed, I can see all versions of the entry in SQL and determine what was changed and when. I am using the query below to select records from my SQL database, but it only works with one date at a time. Because the field 'Logtime' acts as a unique identifier to pull records together, I am uncertain how to query a date range without pulling erroneous duplicate records. I would greatly appreciate any assistance the community can provide. Please let me know if I can clarify my question in any way.

DECLARE @theDate varchar(60)
SET @theDate = '10/31/2018'

SELECT        dbo.tbl_EquipmentRequests.Logtime, dbo.tbl_EquipmentRequests.[Equipment Request ID], dbo.tbl_EquipmentRequests.[TrackIT Number], dbo.tbl_EquipmentRequests.[Requestor name:], 
                         dbo.tbl_EquipmentRequests.[Requestor email:], dbo.tbl_EquipmentRequests.[Entered on:], dbo.tbl_EquipmentRequests.[Approver name:], dbo.tbl_EquipmentRequests.[Approver email:], 
                         dbo.tbl_EquipmentRequests.Status, dbo.tbl_EquipmentRequests.Notes, dbo.[tbl_IT Staff].[IT Staff Member], dbo.tbl_Order_Types.[Order Type], dbo.tbl_EquipReqDetails.Vendor, dbo.tbl_EquipReqDetails.Quantity, 
                         dbo.tbl_EquipReqDetails.[Unit Price], dbo.tbl_EquipReqDetails.[Account Code], dbo.tbl_EquipReqDetails.Status AS Expr1, dbo.tbl_EquipReqDetails.[Vendor Invoice #], 
                         dbo.tbl_EquipReqDetails.[Invoice approved by Tom], dbo.tbl_EquipReqDetails.[Invoice date], dbo.tbl_EquipReqDetails.[Invoice sent to AP on], dbo.tbl_EquipReqDetails.Notes AS Expr2, 
                         dbo.tbl_Equipment.[Equipment Description]
FROM            dbo.tbl_EquipmentRequests INNER JOIN
                         dbo.[tbl_IT Staff] ON dbo.tbl_EquipmentRequests.[Entered by:] = dbo.[tbl_IT Staff].ID INNER JOIN
                         dbo.tbl_Order_Types ON dbo.tbl_EquipmentRequests.[Order Type] = dbo.tbl_Order_Types.ID INNER JOIN
                         dbo.tbl_EquipReqDetails ON dbo.tbl_EquipmentRequests.[Equipment Request ID] = dbo.tbl_EquipReqDetails.[Equipment Request ID] INNER JOIN
                         dbo.tbl_Equipment ON dbo.tbl_EquipReqDetails.[Equipment ID] = dbo.tbl_Equipment.ID
WHERE        (CAST(dbo.tbl_EquipmentRequests.Logtime AS date) = @theDate) AND (CAST(dbo.[tbl_IT Staff].Logtime AS date) = @theDate) AND (CAST(dbo.tbl_Order_Types.Logtime AS date) = @theDate) AND 
                         (CAST(dbo.tbl_EquipReqDetails.Logtime AS date) = @theDate) AND (CAST(dbo.tbl_Equipment.Logtime AS date) = @theDate)

Please tag your post with your SQL database (MS Access?). "SQL" is a generic term, not a database platform. — Elaskanator, Nov 06 '18 at 20:33
change the `=` to `>=` or `<=`. Seems to be all that you would need. And store dates as `date` not `varchar`. — S3S, Nov 06 '18 at 20:37
SQL Server Management Studio (SSMS) is able to query various database platforms (e.g. Azure), not just SQL Server. You should be able to tell from Object Explorer by checking the first couple words in the parenthesis after the server name, or the Product field under the General tab of the server properties. — Elaskanator, Nov 06 '18 at 20:54

Elaskanator · Answer 1 · 2018-11-06T20:46:35.080

Assuming that your time zones are all the same (never save time data without making time zones transparent - using UTC is the recommendation), then use the correct data types as well (using strings for everything is very bad). _{I also highly recommend using ISO-8601 formatting because it is non-ambiguous and big-endian (which makes naïve string sorting so much easier).}

Once you have your date, just add 24 hours to it to get midnight of the next day and do a range query (in SQL Server syntax):

DECLARE @FilterDate DATE = '2018-10-31'
DECLARE--implicit conversions to DATETIME type here:
     @FilterStart DATETIME = @FilterDate
    ,@FilterEnd DATETIME = DATEADD(DAY, 1, @FilterDate)

SELECT ...
FROM ...
WHERE
    @FilterStart <= LogTime
    AND LogTime < @FilterEnd

Note how the end of the time range does not use the LessOrEqual operator, because midnight of the next day is not on your specified date, but every little epsilon before is included (and you don't want to make assumptions about precision of your data types if you can avoid it), so don't use BETWEEN which treats both bounds as inclusive. And if you're using timestamps as unique identifiers, you're gonna have a bad time for that reason (not to mention race conditions). Use foreign keys/autoincrement columns instead.

Also note how there are no functions applied in the WHERE clause (also avoid them in join clauses), which allows the SQL engine to utilize any indexes on the rows. Otherwise casting your LogTime values to DATE would prevent index use.

Date ranges in SQL select query

1 Answers1