Help us to make MyByte work better for you by giving us your suggestions, advice and feedback here.

How useful is MyByte to you now?

Very useful Quiet useful Not very useful Useless

Feedback topic:

Bug Suggestion Compliment Complaint

What can we do to improve MyByte for you....

FEEDBACK
Home | Login
 
 

MyByte is brought to you by > 

Acer CSSA JCSE Apple
 

 
Industry News
  
 
Latest Events
  
 
Select day of the week in an SQL Query
Posted: 9/6/2009 - 3 comment(s) [ Comment ] - 0 trackback(s) [ Trackback ]
Category:

 

Just the other day I was analysing my blog stats and noticed that I had a few large peeks on Wednesdays. Obviously I could not remember what posts were done on Wednesdays. So I needed a way to select data from the database by filtering it and returning only posts posted on Wednesdays. So I used this little gem in SQL.

Diary If you have any serious data in a database you will most likely work a lot with dates. Date values are stored in date table columns in the form of a timestamp. An SQL timestamp is a record containing date/time data, such as the month, day, year, hour, and minutes/seconds.

There is a neat little function in MS SQL called DatePart. It’s syntax is Datepart([date part (Varchar)],[Expression(Datetime)])

So today is Friday, and  a simple test would reveal that.

SELECT DATEPART(WEEKDAY, GETDATE())

Which would return 6. 6 Being day six, if your first day is Sunday, that being day 1. You can check this out by using:

Select @@datefirst

The weekday datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST. This sets the first day of the week.

So with this in hand you can write a little function to return the name of the week days.

----------------------------------------
--- GetWeekDayNameOfDate
--- Returns Week Day Name in English
--- Takes @@DATEFIRST into consideration
--- You can edit udf for other languages
--------------------------------------
CREATE FUNCTION GetWeekDayNameOfDate ( @Date datetime )
RETURNS nvarchar(50)
 
BEGIN DECLARE @DayName nvarchar(50)
SELECT
@DayName =
CASE (DATEPART(dw, @Date) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
RETURN @DayName END GO

We can use that by issuing the following t-sql command which would return Friday for today:

SELECT GetWeekDayNameOfDate(GETDATE()) as WeekDayName

The nice thing about this is that you can now create a function that returns the week day name in your own particular language by just substituting the strings.

But wait. There is an easier way. MSSQL has another neat little date function, it’s called Datename. This can return the actual given weekday name. It has the same syntax as Datepart, Datepart([date part (Varchar)],[Expression(Datetime)])

So to find out what today's name is just issue this command:

Select DATENAME(WEEKDAY, GETDATE()) as Weekday

So my problem was solved, I could now find out which blog posts were causing those fantastic spikes. By doing this, perhaps I could learn something. I could find out what type of posts my readers enjoyed.

I issued the following command on my blog database:

select * from Blog_Entries
where DateName(Weekday,addeddate) = 'Wednesday'
order by addeddate desc

or

select * from Blog_Entries
where DatePart(Weekday,addeddate) = 4
order by AddedDate

Both returned the posts I needed. Problem solved.

 

Robert Bravery is one of our www.MyByte.co.za Thought leaders to subscribe to the Thought Leaders RSS feed click here http://snurl.com/md27j. Still not registered on www.MyByte.co.za ? Join the online network that connects the entire ICT industry in one room –virtually. Simply click here http://www.mybyte.co.za/signup.php its simple and it’s free! Follow us on Twitter @mybyte

 


Total votes: 0
Average: 0