CODEWITHSUNDEEP
sqldate
user974873
user974873

Reputation: 321

How can I get the most recent date in SQL?

I want to make a SQL query that gets todays date and the most recent date from a date column. So if I have three records in my database that have the following dates:

I want the SQL query to return all records for March 8, 2012 and March 2, 2012 (most recent date). How can I do this?

I can date today's date using: 

CONVERT( varchar(100), DATEADD( DAY, 0, getdate() ), 111)

Thank You

Edit: Thanks everyone. I just have one more question. I have created two views:

create view with top dates
CREATE VIEW topDates AS
select DISTINCT TOP 3 replace(CONVERT(VARCHAR(20),date,111),'-','/') AS dates from     CSAResults.dbo.Details

create view dateTwo
select *
from (select ROW_NUMBER() over (order by dates desc) as srNo, dates
    from topDates)
    AS employee
    WHERE srNo=2

And now I want to select * from my DB where a column is equal to the 'dates' column from the view 'dateTwo'

select buildNumber
from CSAResults.dbo.Details
where buildNumber LIKE '%Main '+ (SELECT dates FROM dateTwo) + '%'

But this returns nothing.

Thanks

Upvotes: 0

Views: 708

Answers (4)

Lee Tickett
Lee Tickett

Reputation: 6027

For your second question:

select buildNumber
from CSAResults.dbo.Details
inner join dateTwo
on buildNumber LIKE '%Main '+ dateTwo.dates + '%'

Upvotes: 1

Lee Tickett
Lee Tickett

Reputation: 6027

SELECT *
INTO #TEMP
FROM
(
SELECT GETDATE() DATE_FIELD, 'Blah1...' OTHER_FIELDS
UNION SELECT GETDATE() DATE_FIELD, 'Blah2...' OTHER_FIELDS
UNION SELECT DATEADD(d,-1,GETDATE()) DATE_FIELD, 'Blah3...' OTHER_FIELDS
UNION SELECT DATEADD(d,-1,GETDATE()) DATE_FIELD, 'Blah4...' OTHER_FIELDS
UNION SELECT DATEADD(d,-3,GETDATE()) DATE_FIELD, 'Blah5...' OTHER_FIELDS
) A

SELECT * FROM #TEMP

SELECT * FROM
(
 SELECT DATE_FIELD, OTHER_FIELDS,
 DENSE_RANK() OVER (ORDER BY DATE_FIELD DESC) _RANK
 FROM #TEMP
) A
WHERE A._RANK < 3

Upvotes: 1

Taryn
Taryn

Reputation: 247650

You can do the following:

select date
from yourtable
where 
(
    date = Convert(varchar(10), getdate(), 101)
    OR
    date IN (SELECT Max(date) 
                FROM yourtable
                WHERE date!= Convert(varchar(10), getdate(), 101))
)

Upvotes: 2

Robert Kaucher
Robert Kaucher

Reputation: 1861

Here is an example script that does what you are asking. It uses a sub-query to select all records with MAX on the date. You would just add an OR to also select items for the current date.

    DECLARE @A TABLE
(
part_no VARCHAR(5),
rev CHAR,
on_hand TINYINT,
safety_stock TINYINT,
so_no VARCHAR(5),
so_date DATETIME
)


INSERT @A
SELECT '12345', 'A', 10, 15, 'S1234', '12/14/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1233', '10/01/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1232', '08/02/2009' UNION ALL
SELECT '12346', '',  5, 0, 'S1231', '08/01/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1230', '10/20/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1229', '07/15/2009'

SELECT * FROM @A AS A
WHERE so_date =
(
    SELECT MAX(so_date)
    FROM @A AS B
    WHERE B.part_no = A.part_no AND B.Rev = A.Rev 
)

Upvotes: 1

Related Questions