SQL dates comparision

Transact-SQL
1

Hi All,

How can i compare sql dates in the following method

Create table #temp
(
	Id int,
	startdate datetime
)

insert into #temp values (1, getdate()-2)
insert into #temp values (1, getdate()-5)
insert into #temp values (1, getdate()-7)
insert into #temp values (1, getdate()-9)
insert into #temp values (1, getdate()-10)
insert into #temp values (1, getdate()-30)
insert into #temp values (1, getdate()-32)
insert into #temp values (1, getdate()-35)

select * from #temp

Declare @FromDate datetime = '12/25/2022'
Declare	@ToDate datetime = '12/31/2022'

DEclare @Query nvarchar(max)

set @Query= ' select *  from #temp ' 

if(@FromDate is not null and @ToDate is not null)
	set @Query=@Query+' where Convert(nvarchar(255),startdate,103) >='''+  Convert(nvarchar(255),@FromDate,103) +''' and  Convert(nvarchar(255),startdate,103) <='''+Convert(nvarchar(255),@ToDate,103)+''''

exec(@Query)
drop table #temp

My stored procedure is in the same format the result i received by comparing only day but not the total date

2

Thank you for this informative content this helps me alot

4

No, the from and to dates have dates
12/25/2022 and 31/25/2022

But the result have dates from the month of november(11)

5

please see this

Declare @FromDate datetime = '12/25/2022'
Declare	@ToDate   datetime = '12/31/2022'

Declare @Query nvarchar(max) = ''

IF ( @FromDate is not null and @ToDate is not null  )
set @Query= ' select *  from #temp  where startdate between ''' + cast(@FromDate as varchar) + ''' AND ''' + cast(@ToDate as varchar) + ''''

exec(@Query)

image

6

You don't need to - or even want to - convert dates to strings and then compare. You also do not want to use BETWEEN when comparing datetimes, as that could lead to inconsistent (read: wrong) results.

If you want to compare a column value is within a from and to date range:

SELECT *
  FROM #temp         t
 WHERE t.startdate >= @FromDate
   AND t.startdate <  dateadd(day, 1, @ToDate)  --midnight of next day

Using the next day at midnight for the ToDate will make sure you include all times up to, but not including midnight of the following day. The actual @ToDate value - after the implicit conversion from a string to a datetime would be '2022-12-31 00:00:00.000'. If the startdate was '2022-12-31 00:00:01.000' it would be excluded.

In your sample - you are also using dynamic SQL. There is no reason for dynamic SQL here - but if you are thinking you need dynamic SQL because you may not have either variable, then you should make sure you always have a value:

SET @FromDate = COALESCE(@FromDate, '1900-01-01')
SET @ToDate = COALESCE(@ToDate, '9999-12-31')
1 Like