Function to ignore timestamp in DATE

I am doing a stored procedure and need to use a specific StartDate. The lookup table however DATE values all have date and timestamp. Whenever I set a specific StartDate the query only extracts the records with a 00:00:000 time for that specific date. All the records with a time > than 00:00:000 gets ignored. I need it to extract all the records for that date, regardless the timestamp.

@Parameter1 DATE = convert(date,getdate()-1)


SET @StartDate = @Parameter1

I'll appreciate it if someone can help me on this.


i am showing this .. hope it helps you get idea on what to do !!!

please click arrow to the left for drop create sample data
drop table sample_data

create table sample_data 
column_date_time datetime

insert into sample_data select '2020-08-31 20:44:27.540'
insert into sample_data select '2020-08-31 10:30:22.200'
insert into sample_data select '2020-08-31 09:44:27.540'
insert into sample_data select '2020-08-31 11:14:11.124'

insert into sample_data select '2020-08-30 12:12:27.540'
insert into sample_data select '2020-08-30 11:14:22.200'
insert into sample_data select '2020-08-30 10:56:27.540'
insert into sample_data select '2020-08-30 13:23:11.124'

select   'SQL Output'
       , column_date_time 
   cast(column_date_time as date) = '2020-08-31'


The standard, best-practice method is this:

WHERE table_datetime >= @StartDate AND table_column < DATEADD(DAY, 1, @StartDate)

Don't use functions on table columns, they prevent index seeks (SQL Server has a built-in optimization where it can normally still do a seek on datetime columns you have CAST to a date, but it's better to just avoid using functions on table columns all the time).

Note that this technique applies to other time ranges as well, some examples:
WHERE table_datetime >= '20200801' AND table_datetime < '20200901' --entire month
WHERE table_dateime >= '20200101' AND table_datetime < '20210101' --entire year
SQL will NOT optimize YEAR(table_datetime) (AFAIK, the last time I checked).

1 Like