SQLTeam.com | Weblogs | Forums

How to query with date/time with separate column in sql

Other SQL Server Topics
#1

SAMPLE TABLE STRUCTURE

FldDate - Date
FldTime - SmallInt

SAMPLE RECORD

FldDate       FldTime
2022-01-02    545
2022-01-15    600
2022-01-25    545
2022-01-01    1545

RESULT TO BE ACHIEVE

FldDate       FldTime
2022-01-01    1545
2022-01-02    545
2022-01-15    600

IMAGINATION WRONG CODE

SELECT [FldDate], [FldTime] FROM [Table]
WHERE CONVERT(DATETIME,CONVERT(DATE,[FldDate])+' '+CONVERT(TIME,[FldTime])) >= '2022-01-01 0600'
AND  CONVERT(DATETIME,CONVERT(DATE,[FldDate])+' '+CONVERT(TIME,[FldTime])) <= '2022-01-15 0600'

how to combine separate table for date and time for query. thank you.

#2

You might wanr to look into the Dateadd function

#3

You could gain some efficiency by using both "sargable" conditions on the date column and the full comparison for the datetime:


SELECT [FldDate], [FldTime] 
FROM [Table]
WHERE FldDate >= '2022-01-01' AND 
    FldDate <= '2022-01-15' AND
    DATEADD(MINUTE, FldTime / 100 * 60 + FldTime % 100, CONVERT(DATETIME, FldDate)) 
        >= '2022-01-01 0600' AND
    DATEADD(MINUTE, FldTime / 100 * 60 + FldTime % 100, CONVERT(DATETIME, FldDate)) 
        <= '2022-01-15 0600'
1 Like
#4

thank you so much @ScottPletcher . these is so perfect :heart_eyes:

#5

If you need to do this a whole lot with a particular table, consider converting Scott's good formula to a Persisted Computed Column and slapping an index on it.

#6

hi @JeffModen thank you for your suggestion. may i know how can i slap an index on query.

#7

@xander83

I'd have to know about the table and indexes, data, and see an actual execution plan and also know, do you actually have a performance issue now? My SWAG on an index would certainly involve an index on the FldDate if you follow what Scott's solution is. But...

Like I said, if this is done a lot, I'd take his good formula and use it to make a persisted computed column in the table and then put an index on that. Look up "computed columns" in Google. You should already know how to add an index to a column,

The reasons I'm not writing the code for you to create the computed columns is that I know next to nothing about the table and you will remember "computed columns" better if you look it up and try it yourself. You've gotta have some of the fun on this post. :smiley: