Ok, so I realize this isn't a SQL book, but it would be helpful if someone could help out with the procedure below. Just trying to get a handle on how the code gets the result set it does.

In the following...where is DAY defined? Is that just an internal typecase function...or is there a DAY function somewhere I am not seeing it. Also--is the first three lines generic for any procedure I would need to write? Lastly, what is the "106" parameter passed in the CONVERT function?


*********************************************
Get Days With Events

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[GetDaysWithEvents]
    (
        @DiaryId int,
        @EventFromDate datetime,
        @EventToDate datetime
    )

AS
SET @EventFromDate = CONVERT ( datetime , @EventFromDate , 106)
SET @EventToDate = CONVERT ( datetime , @EventToDate , 106)
SET @EventToDate = DATEADD(d, 1, @EventToDate)

SELECT DAY(EventDate)
  FROM DiaryEvent
  WHERE DiaryId = @DiaryId AND
            (EventDate >= @EventFromDate AND EventDate < @EventToDate)
GROUP BY DAY(EventDate)
ORDER BY DAY(EventDate)


Thanks so much,
Rob

Hi Rob,

1. DAY is a built-in SQL Server function that returns an integer representing the day datepart of the specified date.

So, SELECT DAY(GetDate()) would return 9 for today's date (GetDate() is a function that returns the current date and time)

2. The first three lines aren't required in a procedure you write yourself. However, if you script the procedures using SQL Server, this is what you get. They're basically defaults to ensure the procedure ends up the same as it was in the system when you scripted it. Don't bother typing this in every time you create a procedure.

3. The number 106 is a parameter to the CONVERT function that tells SQL Server to convert the date and time in a dd mon yyyy format.

Take a look at SQL Server's Books Online for more information about 1 and 3. For 1, the keyword is DAY, for 3 the keyword is CONVERT (and CAST) and you'll find a list with all other possible numbers you can pass to Convert.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar,

As always, thank you so much for your time and help!

Kind Regards,
Rob