Comparing Date Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing Date Fields

Author

Topic

rlopez
Starting Member

18 Posts

Posted - 2006-08-11 : 12:13:44

Does anyone know if a function exists is T-SQL where dates from different fields in a row can be compared to find the lowest date? This would be similar to the =MID(,,,) function in Excel.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-11 : 12:39:44

date_1 < date_2

CODO ERGO SUM

rlopez
Starting Member

18 Posts

Posted - 2006-08-11 : 12:42:30

Will this work to compare three dates and find the lowest?

rlopez
Starting Member

18 Posts

Posted - 2006-08-11 : 13:11:04

Actually, I don't think that I am asking this in the right way. I don’t want to write a statement that returns all records where one date is > that the other. What I am trying to do is determine which of three dates for each record is the lowest so that I can use that date in a calculation for each record returned. I am not looking for a statement like.

SELECT *
FROM 'mytable
where date_1 < date_2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-11 : 13:14:41

Select id, min(datecol) from yourtable

or post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

rlopez
Starting Member

18 Posts

Posted - 2006-08-11 : 13:41:17

Here is what I want to do.

SELECT (field_1 * (DATEDIFF(DAY, MIN(datefield_1, datefield_2, datefield_3), datefield_4))

The issue is, I need to find a function do this MIN(datefield_1, datefield_2, datefield_3), this works in Excel, but in SQL the MIN function requires one argument.

field_1 = 100.00

datefield_1 = 1/1/2004
datefield_2 = 1/1/2005
datefield_3 = 1/1/2006
datefield_4 = 7/31/2006

In this case I would like to see:

SELECT 100.00 * DATEDIFF(DAY,'1/1/2004', '7/31/2006')
from mytable = 94200.00

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-11 : 13:52:55

You can wrap this logic in a user defined function if it is more convenient.


Select
	Lowest_Date =
		case
		when date_1 < date_2 and date_1 < date_3
		then date_1
		when date_2 < date_1 and date_2 < date_3
		then date_2
		else date_3
		end
from
	MyTable

CODO ERGO SUM

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-14 : 15:42:20

Or just use multiple combined MIN() calls:


  MIN( MIN( MIN(datefield_1, datefield_2), datefield_3), datefield_4)

Ken

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 16:10:24

quote:
Originally posted by KenW

Or just use multiple combined MIN() calls:

MIN( MIN( MIN(datefield_1, datefield_2), datefield_3), datefield_4)

Ken

That will not work.

The SQL Server MIN function only allows 1 argument.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 06:41:50

Something like this, maybe?

-- prepare test data
declare @test table (id int identity, field1 int, a datetime, b datetime, c datetime, d datetime)

insert	@test
select	111, '20060404', '20060411', '20060830', '20060815' union all
select	29, '20060101', '20061208', '20060104', '20060906'

-- do the work
select	t0.*,
	field1 * DATEDIFF(day, t0.d, 
	(
		select	top 1 z.x
		from	(
				select	a x
				from	@test t
				where	t.id = t0.id
				union all
				select	b
				from	@test t
				where	t.id = t0.id
				union all
				select	c
				from	@test t
				where	t.id = t0.id
			) z
		order by z.x
	)) calc
from	@test t0

Peter Larsson
Helsingborg, Sweden

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-15 : 12:50:54

quote:
Originally posted by Michael Valentine Jones
That will not work.

The SQL Server MIN function only allows 1 argument.

Of course you're right, Michael. Thanks for the correction.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources