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.
DECLARE
@Parameter1 DATE = convert(date,getdate()-1)
DECLARE @StartDate DATE
SET @StartDate = @Parameter1
I'll appreciate it if someone can help me on this.
hi
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
go
create table sample_data
(
column_date_time datetime
)
go
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'
go
select 'SQL Output'
, column_date_time
from
sample_data
where
cast(column_date_time as date) = '2020-08-31'
go
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