Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Oldest date of 4 assigned to variables

Posted on 2014-01-20
Medium Priority
261 Views
Happy Monday!  I am new to SQL coding and I want to look for a condition where the oldest date value is determined and then assigned to a new variable.  Example:

Which of these four is the oldest (by date) and return it as a new variable @Oldest.  These four variables will never have the same value.

@var1 = 01-20-14 12:51:00.000
@var2 = 01-20-14 12:52:00.000
@var3 = 01-20-14 12:53:00.000
@var4 = 01-20-14 12:54:00.000

@Oldest = ????

How would I structure this in SQL?
0
Question by:avatech
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 4

Author Comment

ID: 39794829
Each of these variables is selected from different tables across 4 databases.
0

LVL 66

Assisted Solution

Jim Horn earned 100 total points
ID: 39794834
I'm not aware of a single function that can pull this off, so the only solution I have is to insert all of the variables into a temp table, and then do a MIN
``````Declare @var1 datetime = '01-20-14 12:51:00.000'
Declare @var2 datetime = '01-20-14 12:52:00.000'
Declare @var3 datetime = '01-20-14 12:53:00.000'
Declare @var4 datetime = '01-20-14 12:54:00.000'

Declare @oldest datetime

CREATE TABLE #min (dt datetime)
INSERT INTO #min (dt)
VALUES (@var1), (@var2), (@var3), (@var4)

SELECT @oldest = MIN(dt) FROM #min

-- View the result
SELECT @oldest
``````
0

LVL 16

Accepted Solution

Surendra Nath earned 800 total points
ID: 39794842
The old fashioned way

``````declare @min DATETIME
if @var1 <= @var2
SET @min = @var1
else
SET @min = @var2

if @min <=@var3
SET @min = @var3

if @min <= @var4
SET @min = @var4

select @min
``````
0

LVL 143

Assisted Solution

Guy Hengel [angelIII / a3] earned 100 total points
ID: 39794854
>Each of these variables is selected from different tables across 4 databases.

you could do it with a plan MAX() over the UNION of the 4 tables...
``````select max( dt )
from  ( select date_field dt from table1 ...
union all  select date_field from table2 ...
union all  select date_field from table3 ...
union all  select date_field from table4 ...
) sq
``````
the ... stands for WHERE ( and eventually GROUP BY ) stuff you might need ... just showing the syntax

you can of course do that with variables also:

``````select max( dt )
from  ( select @var1 dt
union all  select @var2
union all  select @var3
union all  select @var4
) sq
``````
0

LVL 4

Author Comment

ID: 39794872
Thanks for the quick responses!  I need to keep it as simple as possible.  I am setting up a condition for NAGIOS to show me the oldest of the four dates.  So far the Old Fashioned seems most appropriate but I do like the first.
0

LVL 4

Author Closing Comment

ID: 39795067
I liked all of the options and appreciate the rapid response.  The best fit for me that I was able to test was the "Old Fashioned" although I liked the temp table using MIN.  The third option seemed very efficient as well -- thank you all for contributing!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month11 days, 7 hours left to enroll