DateTime help?

[vikoch]

I have this query and I got error: ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.")

SELECT Student_id, deadline
from register
where item = 'mm'
and description like '%int%'
and deadline between (convert(varchar(2),month(getdate())+ 3) + '/01/'
+ convert(varchar(4),year(getdate())))
and
(convert(varchar(2),month(getdate())+ 3) + '/31/'
+ convert(varchar(4),year(getdate())))

When I run the below query, I did not get any error.

SELECT Student_id, deadline
from cert_register
where registration_item = 'ctsp'
and description like '%mainten%'
and deadline between (convert(varchar(2),month(getdate())+ 3) + '/01/'
+ convert(varchar(4),year(getdate())))
and
(convert(varchar(2),month(getdate())+ 4) + '/31/'
+ convert(varchar(4),year(getdate())))

I just changed "month(getdate())+ 4)" instead of "month(getdate())+ 3)

Did anybody know why?
Thanks in advince!

 

[jbenson001]

What exactly are you trying to do?

 

[ousoonerjoe]

I see the error... something that trips me up a lot too. There are only 30 days in September. You have hard coded 31 as the last day of the month. Won't always work. Can't use 30 (February).

Try simplifying the WHERE clause:

WHERE (MONTH(deadline) = MONTH(GETDATE()) AND YEAR(deadline) = YEAR(GETDATE()))

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[vikoch]

We run a proc. beginning of every month to select all records with deadline dates (today date + 3 months)
example: we need all records for November.
I hope it helps.
Thanks

 

[vikoch]

Thanks a lot, It works for now.
What do I do if I run my proc. in November or December?
Thanks again.

 

[ousoonerjoe]

There's nothing to change for the different months in the example i show. It doesn't care about the number days in the month, only the month and year. You could change GETDATE() to an argument and pass a date in.

CREATE PROCEDURE sp_DeadLines (@RptMonth DATETIME = '1/1/1900')

AS

IF @RptMonth = '1/1/1900' SET @RptMonth = GETDATE()

SELECT Student_id, deadline
FROM register  
WHERE item = 'mm'
	AND description LIKE '%int%'
	AND (MONTH(deadline) = MONTH(@RptMonth) AND YEAR(deadline) = YEAR(@RptMonth))

If no date is passed it uses the current month. If a date is passed into it, then it uses the month and year from that date to get all deadlines for that month. This way you can use the same procedure for future requests as well as current.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[vikoch]

I am sorry, my brain does work.

Still did not understand - How... with running this query in Octover, 2008, I will select all record for January, 2009?

I need to run my proc. in the beginnig of October and select all record for January, 2009; Then, I need to run in November 2008 and get all records for February 2009; and so on...
Thanks.

 

[ousoonerjoe]

EXEC sp_DeadLines '1/1/2009' [COLOR=green]--Returns Jan 2009[/color]
EXEC sp_DeadLines '2/1/2009' [COLOR=green]--Returns Feb 2009[/color]
EXEC sp_DeadLines            [COLOR=green]--Returns Current Month[/color]

Adjust the stored proc if you really need it to only produce results for 3 months down the line. I was just making a suggestion that was a little more flexible.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.