gettimg max datetime<max(datetime)

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)

gettimg max datetime<max(datetime)

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-08-17 : 15:37:14

i want to get the max datetime value
that is less then the max(datetime)value in a column,which
means the the max(datetime) that is lesss then the max one.
for example :
15/06/2007 15:13.54
13/06/2007 11:13.54
12/06/2007 17:13.54
16/06/2007 05:13.54

in this case i wil get : 15/06/2007 15:13.54

thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-08-17 : 15:55:29

DECLARE @MT TABLE (
Tdate char(30)
)

INSERT @MT

SELECT '15/06/2007 15:13.54' UNION ALL
SELECT '13/06/2007 11:13.54' UNION ALL
SELECT '12/06/2007 17:13.54' UNION ALL
SELECT '16/06/2007 05:13.54'

SELECT MAX(tdate) FROM @MT WHERE Tdate NOT IN (SELECT MAX(Tdate) FROM @MT)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-17 : 16:08:05

quote:
Originally posted by sqlfresher2k7
...
Tdate char(30)
...

That's a very poor choice of datatype to hold a datetime. A value of '15/06/2007 15:13.54' would come before '17/06/2006 15:13.54' using char(30)

A datetime is the best way to go.

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-08-17 : 16:25:18

thnas alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-18 : 01:32:49

quote:
Originally posted by sqlfresher2k7

DECLARE @MT TABLE (
Tdate char(30)
)

INSERT @MT

SELECT '15/06/2007 15:13.54' UNION ALL
SELECT '13/06/2007 11:13.54' UNION ALL
SELECT '12/06/2007 17:13.54' UNION ALL
SELECT '16/06/2007 05:13.54'

SELECT MAX(tdate) FROM @MT WHERE Tdate NOT IN (SELECT MAX(Tdate) FROM @MT)

Is there any reason for not using proper DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 04:41:39

I think the original poster do have proper datatype.

SELECT MIN(Tdate) FROM (SELECT TOP 2 Tdate FROM Table1 ORDER BY Tdate DESC) AS d

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-18 : 04:45:56

quote:
Originally posted by Peso

I think the original poster do have proper datatype.

SELECT MIN(Tdate) FROM (SELECT TOP 2 Tdate FROM Table1 ORDER BY Tdate DESC) AS d

E 12°55'05.25"
N 56°04'39.16"

You posted what I thought

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 10:45:23

Maybe my neuro-bot is almost self-aware and reaching consiousness?

Some kind of premature SkyNet?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 10:52:54

HEY! I hold the ESP supremacy around here!
if you go skynet on me i'll have to take reciprocal action.

you have been warned so hereafter avoid this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-08-18 : 14:50:36

quote:
Originally posted by Peso

I think the original poster do have proper datatype.

peso i wrote :

quote:
i want to get the max datetime value
that is less then the max(datetime)value in a column

have a great week
Peleg:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 18:51:25

Have a great week too!

Nowhere you wrote which kind of DATATYPE you are using. You only wrote datetime VALUE (which could be almost any kind of datatype representing the datetime value you want, such as VARCHAR, VARBINARY, INT and so on).

E 12°55'05.25"
N 56°04'39.16"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-08-19 : 02:32:43

sorry if i wasnt clrea
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

johnsql
Posting Yak Master

161 Posts

Posted - 2007-08-20 : 14:05:06

select max(Tdate) from
(
select Tdate from @mt where tdate < (select max(tdate) from @mt)
) w

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources