Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Filter sql on date and time

Hello ,
I have a query which filters on date
SELECT   
  [Job #]
, [Date Time]
, Car

FROM         tableA
WHERE (([Date Time] >= ''' + CAST(@SelectedDateTime AS NVARCHAR(24)) + ''') 
   OR (Car = '''')
   OR (Driver = '''')) '

Open in new window


How can I filter on date and time.
The requirement is that : WHERE (([Date Time] >= ''' + CAST(@SelectedDateTime AS NVARCHAR(24)) + ''')  and also the time .
Check the time now and display records which are from time.now.
Example;
 Time now is 11:30 ; display records from 11:30 onwards .

Regards
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Rias,

Please use like below -

DECLARE @SelectedDateTimeFrom AS DATETIME = GETDATE()
DECLARE @SelectedDateTimeTO AS DATETIME = GETDATE()

SET @SelectedDateTimeFrom = CAST(CAST(CAST(@SelectedDateTimeFrom AS DATE) AS VARCHAR(10)) + ' 11:30:00' AS DATETIME)
SET @SelectedDateTimeTO = CAST(CAST(CAST(@SelectedDateTimeTO AS DATE) AS VARCHAR(10)) + ' 11:35:00' AS DATETIME)


SELECT   
  [Job #]
, [Date Time]
, Car

FROM  tableA
WHERE (

		(
			[Date Time] >= '''' + CAST(@SelectedDateTimeFrom AS NVARCHAR(24)) + ''''
		AND [Date Time] <= '''' + CAST(@SelectedDateTimeTO AS NVARCHAR(24)) + ''''
	
		) 
	   OR (Car = '''')
	   OR (Driver = '''')
   
   )

Open in new window

If you just want >= 11:30 then you can use below-

Explanation - First get the date and add your time in there and add that in the filter condition.


For range you can use above comment.

DECLARE @SelectedDateTime AS DATETIME = GETDATE()

SET @SelectedDateTime = CAST(CAST(CAST(@SelectedDateTime AS DATE) AS VARCHAR(10)) + ' 11:30:00' AS DATETIME)

SELECT   
  [Job #]
, [Date Time]
, Car

FROM  tableA
WHERE (

		(
			[Date Time] >= '''' + CAST(@SelectedDateTime AS NVARCHAR(24)) + ''''
			
		) 
	   OR (Car = '''')
	   OR (Driver = '''')
   
   )

Open in new window

Avatar of RIAS

ASKER

Thanks Pawan,

I really do not need 11:30:00. I need the time now and then filter any suggestions?
Avatar of RIAS

ASKER

Can i have date.time ot time.now. Any suggestions on syntax?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

Thanks! will try and brb
Avatar of RIAS

ASKER

Worked liked Charm mate!!
Avatar of RIAS

ASKER

Thanks a lot!
welcome. Cheers :)