Please help with dates 3

[developer77]

Hello everyone,

I've pasted a part of my stored procedure below:


-- Declare month, year
DECLARE @Month int
DECLARE @Year int

-- Initialize SQL environment
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF

-- Strip off the time part of the date.
SELECT @Date = substring(convert(varchar, @Date, 100), 1, 12)
SELECT @Month = Month(@Date), @Year = Year(@Date)

-- Get information
SELECT P.Firstname, P.Lastname, P.SSN, P.Address, J.Title,
J.Salary
FROM People P, Job J
WHERE P.ID = J.PeopleID
AND Month(J.HireDate) = @Month ) AND ( Year(J.HireDate) = @Year )


Right now it's looking at one date parameter @Date which is a datetime datatype. I need to add another parameter for the end date so that this we can query by a date range. I plan to add another paramter called EndDate and strip off the time like the @Date parameter. However, I'm not sure how to do a between for month and year. For example right now I have it comparing the parameter month and year to the HIREDATE month and year. How do I do a between for this when I add another end date parameter? Thanks in advance for your help.

 

[AlexCuse]

I think you would be better off using DateAdd to return the beginning of the month you want. Use something like this:

select dateadd(m, datediff(m, 0, getdate()), 0)
[code]

Treating dates and strings interchangeably is dangerous ground.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Another easy way to strip the time off the DateTime is

Select Convert(varchar(10),GetDate(),101)
--Limit the varchar to get just the date

--To convert back to datetime with midnighthour
Select Cast(Convert(varchar(10),GetDate(),101) as DateTime)

--To compare just the date part to a real date in MyTable
WHERE Convert(varchar(10),GetDate(),101) >= Convert(varchar(10),MyTable.MyDate,101)
[code]

The thing you have to be careful with on the last one is that if you've got invalid data in that column, the evaluation on Varchar will mess up because it will do it by alpha sorting, not numeric / date sorting.  So, Casting back to DateTime with the midnight time for comparisions is always best if you're not sure.



Catadmin - MCDBA, MCSA
"No, no.  Yes.  No, I tried that. Yes, both ways.  No, I don't know.  No again.  Are there any more questions?"
   -- Xena, "Been There, Done That"

 

[SQLBill]

Always, always, give the length for VARCHAR (along with CHAR, NVARCHAR, and NCHAR).

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Notice that says CAST not CONVERT.

Regardless, it's always good practice to include the desired length.

-SQLBill

Posting advice: FAQ481-4875

 

[developer77]

Thank you all very much for your valuable advice...

 

[Catadmin]

Glad we could help. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"