Case or not to case... 1

[bla1979]

I am pretty sure I sure use case, but not 100% if it will be the best way. Here's what I got, I have a blog site, I want to create a query that shows the 10 most recent blogs, But I want to change the value in the date column to show if it posted "Today" or "yesterday" or "2 Days" or "+3 Days Ago". So here is what I got so far, not really sure how to continue. I have to DATEADD because the server my site is hosted on is 3 hours behind me.

Select blog_id, blog_title, Recent =  Case When DateDiff(Hour, blog_date , DATEADD(hh,+3,GetDate())) < 24 Then 'Today'
ELSE 'Unknown'
END,
From blog_blog

 

[TysonLPrice]

just looking during my lunch break...Could you set the date ranges as variable first and then check them in your case?

something like

@today = getdate()
@yestreday = Getdate() and calculate the time frame

and so on.

The in the case check the dae

when blog_date < @today
when blog_date < @yestreday

Just pseudo coding an idea..

 

[gmmastros]

Take a look at this example. It should show you, generally, how to accomplish this.

Declare @Temp Table(TheDate DateTime)

Insert Into @Temp Values('2006-06-09 10:00 AM')
Insert Into @Temp Values('2006-06-09 9:00 AM')
Insert Into @Temp Values('2006-06-08 9:59 AM')
Insert Into @Temp Values('2006-06-07 11:00 AM')
Insert Into @Temp Values('2006-06-06 5:00 PM')
Insert Into @Temp Values('2006-05-09 9:00 AM')

Select *, DateDiff(Hour, TheDate,GetDate()) / 24,
		Case DateDiff(Hour, TheDate,GetDate()) / 24
             When 0 Then 'Today'
             When 1 Then 'Yesterday'
             When 2 Then '2 days ago'
             Else Convert(VarChar(10), DateDiff(Hour, TheDate,GetDate()) / 24) + ' days ago'
        End
from @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bla1979]

Hey Guys Thanks for all the answers, I am ending up going with

select blog_id,blog_author, Blog_title,blog_date,
	  Case When DateDiff(dd, blog_date,GetDate()) < 1 Then 'Today'
	  When DateDiff(dd, blog_date,GetDate()) = 1  Then 'Yesterday'
 	  When DateDiff(dd, blog_date,GetDate()) = 2  Then '2 Days ago'
	  When DateDiff(dd, blog_date,GetDate()) = 3  Then '3 Days ago'
	  When DateDiff(dd, blog_date,GetDate()) = 4  Then '4 Days Ago'
	 When DateDiff(dd, blog_date,GetDate()) = 5  Then '5 Days Ago'
	 When DateDiff(dd, blog_date,GetDate()) = 6  Then '6 Days Ago'
	 When DateDiff(dd, blog_date,GetDate()) = 7  Then 'Week Ago'
	 When DateDiff(dd, blog_date,GetDate()) > 7  Then 'Weeks Ago'
	Else 'Not Today'
	End as 'Notnew'
From Blog_blog
Order by Blog_ID DESC

I think I am going to consolidate teh code down to what George(gmmastros) wrote. Thanks again, Good Weekend.

 

[SQLDenis]

Now if you want to be fancy
you would create a lookup table with numbers and descriptions
then JOIN the 2 tables by using
From Blog_blog b JOIN NewTable n on n.NumberId =DateDiff(dd, b.blog_date,GetDate())

that way you don't need to do the case you would just display n.TimeDescription

..
..
..
Just a suggestion



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

There's some weirdness using the day parameter for your DateDiff. You may actually prefer it this way, but just be aware that it's happening.

If you do DateDiff between now and 1 minute before midnight, you get something like 13 hours, but since it happened before midnight, it will show as a full day.

Select DateDiff(day, '2006-06-09 12:00 AM', '2006-06-09 1:21 PM')
Select DateDiff(day, '2006-06-08 11:59 PM', '2006-06-09 1:21 PM')


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom