searching for latest date and time only -- sql server (alternate)

jasuk NO[at]SPAM postmaster.co.uk
1/29/2004 8:23:26 AM

[quoted text, click to view]

David Portas
1/29/2004 6:11:31 PM

What data types are you using here? SQLServer DATETIME stores both data and
time in the same column so I don't understand why you appear to have
separate columns. It helps to clarify your requirements if you include DDL
with your questions.

Assuming you define your table with a single DATETIME column you can get the
result you require quite easily:

CREATE TABLE Sometable (id INTEGER, hs_change_dt DATETIME, PRIMARY KEY /*
??? */ (id, hs_change_dt))

SELECT id, MAX(hs_change_dt) AS hs_change_dt
FROM Sometable
GROUP BY id

--
David Portas
SQL Server MVP
--

jasuk NO[at]SPAM postmaster.co.uk
1/30/2004 1:28:23 AM

The database is already setup with two separate columns - one for
date and one for time. see below

[quoted text, click to view]

[quoted text, click to view]

David Portas
1/30/2004 11:03:17 AM

[quoted text, click to view]

OK, but what are the data types? You still haven't told us and it makes a
difference to the solution. I'll assume you have DATETIME for the date and
CHAR(8) for the time.

CREATE TABLE Sometable (id INTEGER, hs_change_date DATETIME, hs_change_time
CHAR(8) NOT NULL, PRIMARY KEY (id, hs_change_date, hs_change_time))

SELECT id,
CAST(DATEDIFF(D,0,hs_change_dt) AS DATETIME) AS hs_change_date,
CONVERT(CHAR(8),hs_change_dt,108) AS hs_change_time
FROM
(SELECT id,
MAX(CAST(CONVERT(CHAR(11),hs_change_date,126)+
hs_change_time AS DATETIME))
FROM Sometable
GROUP BY id) AS T (id,hs_change_dt)

This seems like a pointless design that wastes at least 8 bytes of storage
per row and worst of all it makes the data very difficult to maipulate (see
above and compare it with my first answer).

--
David Portas
SQL Server MVP
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : searching for latest date and time only