SQL date compare

Question

I have a table with 1000 records. One column is the publish date which takes the format '2008-01-02 00:00:00.000'. I want to query the SQL DB to get the record with the latest publish date. should i just do a compare or there is some other filter?

So, is `2008-01-02 00:00:00.000` type `DATETIME` or a `CHAR`?. If its not `DATETIME`, it represents January second or February first? — Lamak, Apr 17 '12 at 18:57

score 4 · Accepted Answer · answered Apr 17 '12 at 18:55

4

SELECT * FROM tbl WHERE publishdate = (SELECT MAX(publishdate) FROM tbl)

answered Apr 17 '12 at 18:55

Cade Roux

85,870
40
177
264

Thanks, BTW how do i convert this publish date to 'mm/dd/yy' in SQL? – Geek Apr 17 '12 at 19:13
1

@javaiText `CONVERT(varchar(8), publishdate, 1)` -- see http://msdn.microsoft.com/en-us/library/ms187928.aspx – Cade Roux Apr 17 '12 at 19:29

score 3 · Answer 2 · answered Apr 17 '12 at 18:58

3

If you want just one record:

SELECT TOP 1 * FROM mytable ORDER BY publishdate DESC

If you want ALL books with the highest publish date, use Cade Roux's query.

answered Apr 17 '12 at 18:58

David

70,778
16
128
169

how do i convert this publish date to 'mm/dd/yy' in SQL? – Geek Apr 17 '12 at 19:21
1

See this link on formatting dates, but bear in mind that after you format it, the returned date will actually be a text (varchar) datatype, not a Date datatype. A date is what it is, and you can't change how the server stores it, you can only use formatting to tell SQL Server to display it in a certain way. http://stackoverflow.com/questions/759292/to-change-date-format-in-sql – David Apr 17 '12 at 19:23

score 0 · Answer 3 · answered Apr 17 '12 at 18:59

0

If publishdate is datetime

SELECT TOP 1  *
FROM tbl
ORDER BY publishdate DESC

answered Apr 17 '12 at 18:59

YvesR

5,494
6
43
67

SQL date compare

3 Answers3