Another ordering Date question

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

General SQL Server Forums

New to SQL Server Programming

Another ordering Date question

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-25 : 10:27:18

How do I get this stored procedure to give me 01/22/2010

I have January dates in the database but they are not coming out I'm getting 12/25/2009 as the max (weekdat).

select max(weekdat), max(dowrdat) as dowrdte
from test

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:30:16

which stored proc? i cant see any.
may be what you need is


select max(weekdat)
from test
where weekdat > '201010101'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-25 : 10:30:28

what datatype is the column and can you post the query?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-25 : 10:35:08

Sorry about that here it is:

Select distinct max(weekdat) as weekdte, (convert(char,max(weekdat),101)) as weekdat,
"dowrdat" = case
when max(weekdat) = offices.dbo.endofmonth(weekdat)
then (convert(char,max(weekdat),101))
end

into #tempupdate
from dbo.ClearedDiary
group by weekdat
order by weekdte desc

select max(weekdat) as weekdte, max(dowrdat) as dowrdte
from #tempupdate

drop table #tempupdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:37:12

quote:
Originally posted by JJ297

Sorry about that here it is:

Select distinct max(weekdat) as weekdte, (convert(char,max(weekdat),101)) as weekdat,
"dowrdat" = case
when max(weekdat) = offices.dbo.endofmonth(weekdat)
then (convert(char,max(weekdat),101))
end

into #tempupdate
from dbo.ClearedDiary
group by weekdat
order by weekdte desc

select max(weekdat) as weekdte, max(dowrdat) as dowrdte
from #tempupdate

drop table #tempupdate

why are you converting date to char? i think thats the problem
you need to worry about format only at front end so dont change datatype for that

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-25 : 10:42:08

Thanks that was it!

Select distinct max(weekdat) as weekdte, max(weekdat) as weekdat,
"dowrdat" = case
when max(weekdat) = offices.dbo.endofmonth(weekdat)
then (weekdat)
end
into test
--into #tempupdate
from dbo.ClearedDiary
group by weekdat
order by weekdte desc

select (convert(char,max(weekdat),101)) as weekdte, max(dowrdat) as dowrdte
from test

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:43:16

gr8

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 13:20:54

I thought ordering a date was only legal in Nevada

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources