String comparison - 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)

String comparison

Author

Topic

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2006-06-20 : 06:32:14

Hi friends,

I have a table (t1) which one of its columns (c1) type is as char(10)
The c1 holds date of sending orders to company::
c1
-----
9/1/2006
12/2/2005
29/3/2003
Now, I wanna to know that how can I get the maximum value of c1?
when I use MAX() function it returns wrong value? what is your solutions?
Thank you very much

sonia

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 06:38:07

Either

Change your table so that it uses the correct data type (preferable).

OR

--data
declare @t1 table (c1 char(10))
insert @t1
          select '9/1/2006'
union all select '12/2/2005'
union all select '29/3/2003'

--calculation
set dateformat dmy
select max(cast(c1 as datetime)) from @t1

/*results
2006-01-09 00:00:00.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 06:40:19

Use Proper DATETIME datatype to avoid this type of problems

You need to convert it to DateTime datatype before doing any calculations

Select MAX(cast(c1 as datetime)) from yourTable

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 06:41:19

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 07:04:49

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2006-06-20 : 07:10:44

That is true. but unfortunately, I have records in t1 like below::
c1
------
9/2/1356
27/3/1380

and as you know it's impossible converting to datatime datatype because it's out of range.
Indeed, what was your image about , mad...?!!

sonia

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 07:49:30

You could store an adjusted date value as a datetime (e.g. date + 1000 years). It will probably be easier to work with that way.

Off the top of my head, here's one way you could do your calculation with 'invalid' datetimes. You may need to change the adjustment.

--data
declare @t1 table (c1 char(10))
insert @t1
          select '9/2/1356'
union all select '27/3/1380'

--calculation
declare @adj int
set @adj = 1000

set dateformat dmy
select stuff(v, len(v)-3, 4, right(rtrim(v), 4)-@adj)
from (
    select convert(varchar, MaxC1Adjusted, 103) as v
    from (
        select max(cast(stuff(c1, len(c1)-3, 4, right(rtrim(c1), 4)+@adj) as datetime)) as MaxC1Adjusted
        from @t1) a) b

/*results
27/03/1380
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources