Convert numbers to day names

[klunde]

Hi

I have a sql query that returns numbers from 0 to 14. When I'm showing the result on my asp page I would like to convert these numbers into

0 = "Today", 1 = "Yesterday", 2 = {the name of the day before yesterday (if ran today it would be "sunday"} and so on.

Anyone know how I can accomplish this?

</Morten>

 

[PaulBricker]

In the query that returns your values, you could add an additional column that calculates the value you want. Something like

MyDay:IIf([FieldName]=0,"Today",IIf([FieldName]=1,"Yesterday",IIf([FieldName]=2,Format(Date(),"dddd").....)))

It gets kind of long with 14 IIf() statements strung together, but should do the trick. You might be able to double up the numbers to cut down on the length of the expression. LIke this

MyDay:IIf([FieldName]=0,"Today",IIf([FieldName]=1,"Yesterday",IIf([blue][FieldName]=2 or [FieldName]= 9,[/blue]Format(Date(),"dddd").....)))

Then just collect that value from your query witht the ASP page.

Paul

 

[Chopstik]

You could probably do this in either the data call (from the database side) or on the front-end (ASP side). A lot of it just depends on how you have set your application up. If you're using SQL, you can convert the numbers into text with a simple CASE WHEN statement.

Similarly, on the ASP side, you could use a SELECT CASE statement on that particular value. These are generic answers but without a little more knowledge of what you've done, it's hard to provide you with a better solution. Paul's solution should also work but there are probably other alternatives, too.

------------------------------------------------------------------------------------------------------------------------
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

http://lessthandot.com

 

[DreXor]

response.write FormatDateTime(Date()-YOURINTEGER,1))


use left, instr, split whatever to find the space between the named day and the rest of the date.

another option is:
ThisDay = Weekday(date()-YOURINTEGER) then using

DaysArr = Array("error","sunday","monday","tuesday........)

Response.Write DaysArr(ThisDay)

note use of error in the array because weekday will almost never return a zero

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[klunde]

Actually, what I ended up with is this:

for i = 2 to 14
	Response.write "<td bgcolor=""" & iif(weekday(dateadd("d",-i,date()),2)>5,bg1,bg2) & """>" & WeekDayName(weekday(dateadd("d",-i,date())),true) & " " & day(dateadd("d",-i,date())) & "/" & month(dateadd("d",-i,date())) & "</td>"
next

</Morten>

 

[DreXor]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005