date issue in 2000 - SQL Server Forums

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)

date issue in 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-20 : 15:40:36

[code]

Please help me in rewriting the below code to get the output.
I want the greater date..

DECLARE @MT TABLE

(

TNO Int,

dt1 varchar(13)

)

INSERT INTO @MT (TNO, dt1)

(
Select '1' , '070710162816'
Union All
Select '2', '071210162816'
)

DECLARE @HT TABLE

(

TNO Int,

dt2 varchar(13)

)

INSERT INTO @HT (TNO, dt2)

(
Select '1' , '070710162816'
Union All
Select '2', ''
)

select C.*
from @MT C
join @HT D ON
D.TNO = C.TNO
where
convert(datetime,'20'+Stuff(Stuff(Stuff(C.dt1,11,0,':'),9,0,':'),7,0,' '))
>
convert(datetime,'20'+Stuff(Stuff(Stuff(D.dt2,11,0,':'),9,0,':'),7,0,' '))

Expected output:

TNO dt1
--- ------
2 071210162816

[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-20 : 15:55:36

Try this:

select C.*
from @MT C
join @HT D ON
D.TNO = C.TNO  
where 
convert(datetime,'20'+Stuff(Stuff(Stuff(C.dt1,11,0,':'),9,0,':'),7,0,' '))
>
COALESCE(convert(datetime,'20'+Stuff(Stuff(Stuff(D.dt2,11,0,':'),9,0,':'),7,0,' ')), CAST(0 AS DATETIME))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 17:50:01

You are SO overcomplicating things...

SELECT		c.*
FROM		@MT AS c
INNER JOIN	@HT AS d ON d.TNO = c.TNO  
WHERE		c.dt1 > d.dt2

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-21 : 12:19:41

Thanks !!
date Conversion is not requried since the value is storing as varchar.. 070710162816Will it not be a problem

WHERE c.dt1 > d.dt2

instead of
where
convert(datetime,'20'+Stuff(Stuff(Stuff(C.dt1,11,0,':'),9,0,':'),7,0,' '))
>
convert(datetime,'20'+Stuff(Stuff(Stuff(D.dt2,11,0,':'),9,0,':'),7,0,' '))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 14:42:24

No, it shouldn't. Since all strings are in format YYMMDDHHMMSS.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources