Compare two dates

[NeelyJ]

I need to compare 2 dates and find the minimum of the two. something like min(@date1, @date2). I am sure there is an easy way to do this, but I can't seem to figure it out. Thanks.

 

[ClaireHCM]

Try this

create function dbo.compare_date (@date1 datetime ,@date2 datetime)
returns varchar(500)
as begin

declare @datediff int
declare @day1 datetime
declare @day2 datetime
declare @string varchar(500)
set @day1 = @date1
set @day2 = @date2
set @datediff = datediff(d,@day1,@day2)
if @datediff<0
begin
set @string = convert(varchar(12),@day2,101)+&quot; is the minimum of the two dates&quot;
end
else
begin
set @string = convert(varchar(12),@day1,101)+&quot; is the minimum of the two dates&quot;
end

return (@string)
end

--usage: select dbo.compare_date ('2/23/2000','2/1/2001')

claire

 

[topher0303]

If you are wanting to use it in a select statement, you can use a case statement:

SELECT CASE WHEN col1 < col2 THEN col1
ELSE col2
END AS MinColValue,
col3, ...
FROM table

Chris.

 

[osjohnm]

Hi

Since you are using variables then it makes it easier. You can do the following:

if datediff(mi, @date1, @date2) > 0
select @date1
else
select @date2

I used minutes in the datediff as I thought it might be more accurate than days.

You can make use of a user-defined function which you will be able to use again and again from multiple stored procedures but if this is a once off query or only one procedure then a udf might be a bit of an overkill.

Also be warned that ClaireHsu function works well but she is using a day difference as opposed to going lower eg minutes or seconds. Because there is a varchar conversion even if you pass in datetime data which I assume your @date1, @date2 are the minutes and seconds are ignored.

eg: 2/23/2000 12:15.00 - 2/23/2000 12:30:00

Both of these will be seen as 2/23/2000

Hope this helps

John

 

[topher0303]

Why not simplify it even further and make it even more accurate:

if @date1 < @date2
select @date1
else
select @date2

This will make it accurate down to the level in which datetime tracks time.

Chris.

 

[NeelyJ]

Here is the query. Maybe it will help explain the problem.

declare @Store_number int
declare @Date_Start datetime
declare @Date_Stop datetime
declare @Date Datetime
set @store_number = 4
set @Date_Start = '1/1/03'
set @Date_Stop = '2/1/03'
-- Determine the last date a cashier's report is posted for
declare @date datetime

if getdate() < @Date_stop
begin
set @date = getdate()
end
else
set @date = @DateStop
select @date

SELECT DAILY_SALES.STORE_NUMBER, CAFETERIA.CAFETERIA,
CURR_DATE,
SUM(NET_SALES_CALC) AS ThisYR_net,
SUM(TOTAL_CUSTOMER_COUNT) AS ThisYR_CC,
CURR_DATE - 364 AS last_date,
(SELECT SUM(net_sales_calc) FROM dbo.DAILY_SALES AS lastsales WHERE lastsales.curr_date = daily_sales.curr_date - 364 AND daily_sales.store_number = lastsales.store_number) AS lastYR_net,
(SELECT SUM(Total_customer_count) FROM dbo.DAILY_SALES AS lastsales WHERE lastsales.curr_date = daily_sales.curr_date - 364 AND daily_sales.store_number = lastsales.store_number) AS lastYR_CC,
(select SUM(NET_SALES_CALC) - (SELECT SUM(net_sales_calc) FROM dbo.DAILY_SALES AS lastsales WHERE lastsales.curr_date = daily_sales.curr_date - 364 AND daily_sales.store_number = lastsales.store_number)) as Salesdiff,
(select SUM(TOTAL_CUSTOMER_COUNT) - (SELECT SUM(Total_customer_count)FROM dbo.DAILY_SALES AS lastsales
WHERE lastsales.curr_date = daily_sales.curr_date - 364 AND daily_sales.store_number = lastsales.store_number)) as Customer_Diff FROM dbo.DAILY_SALES, CAFETERIA
where daily_sales.store_number = @Store_number
AND DAILY_SALES.STORE_NUMBER = CAFETERIA.STORE_NUMBER
and curr_date between @Date_Start and
--***********************************************
(select max(curr_date) from daily_sales as ds where ds.store_number = daily_sales.store_number)
GROUP BY DAILY_SALES.STORE_NUMBER, Curr_date, CAFETERIA.CAFETERIA
Order by daily_sales.Store_number
GO

 

[NeelyJ]

Should have explained further. It is a stored procedure to feed a datagrid. The query compares this year to last year. I only want a comparison for days that have been entered for this year. ie, on the first day of the month, don't want to show 1 day vs the previous year's 30. Just want to compare day 1 to day 1. The problem is determining whether the site has entered their information for the current day (and whether to include the corresponding day last year) or not, and then if they are asking for a date range that ends before today. The date problem is where the stars are.