Datepart

[Vi08]

Hello,

I am using reporting 2008. Here is our formulas:
datepart (month, date_of_birth) * 100 + datepart (day, date_of_birth)
between (@tb_StartBirthMonth * 100) + @tb_StartBirthDay
and (@tb_EndBirthMonth * 100) + @tb_EndBirthDay.

This formulas works fine if people entering dates in this order from Jan thru December. For example, if my birth date is December 5th using the following parameters:
@tb_StartBirthMonth: 12
@tb_StartBirthDay: 2
@tb_EndBirthMonth: 12
@tb_EndBirthDay:15
Since my birth date is in the range entered, it will return mine birth date using the above formulas. That is (12*100) + 5 between (12*100) + 2 and (12*100 )+ 15.
1205 between 1202 and 1215 ? YES.
However, the problem arise if people entering parameters in this order for example:
@tb_StartBirthMonth: 12
@tb_StartBirthDay: 2
@tb_EndBirthMonth: 1
@tb_EndBirthDay:31
This is because they want to see a list of people with birth date in December + those will be in Januray of next year for example. My above formulas is no longer working. It does not return my birth date even though it is in the range entered.
That is (12*100) + 5 between (12*100) + 2 and (1*100 )+ 31
1205 between 1202 and 131 ? NO.
Instead of asking people to run one for December and another for January, is there away I can fix the formulas where it can compute either ways? Any help to can provide is greatly appreciated. I will try to shorten my future questions next time. Sorry

 

[simian336]

Just checking my assumptions here... You want to see the word 'YES' if a date is between 2 dates and 'No' if it not between 2 dates? Correct?

Simi

 

[Vi08]

I am so sorry for the confusion. Yes or No were just my validation. Basically, I want to be able to get people birth date when their dates are in the range entered regardless they enter how they enter month start and month end.

 

[gmmastros]

This sounds a bit complicated, but interesting reports usually are. I would probably approach the problem like this:

[tt]
If Start Month and day < end month and day
Use your existing formula
Else
Get rows where:
birth date between end month and day and dec 31
or
birth date between jan 1 and start month and day
[/tt]

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Vi08]

Sorry, I am a little bit slow here:-(

Dec 31 or Jan 1 are just my example that they are variable. They could be any months or dates like November thru Jan or Feb or March.... or October thru jan, feb, march and etc.

birth date between end month and day and dec 31 or birth date between jan 1 and start month and day. Since dec 31 or Jan 1 are variable that they could be some other month and date, how can I incorporate them? Thanks so much.

 

[Qik3Coder]

Select 'yes'
WHERE
--Happy Check
(@bDate >= @sDate AND @bDate <= @eDate) OR
--Wrap Around
(@eDate < @sDate AND (@bDate >=@sDate OR @bDate < @eDate))
HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Qik3Coder]

...Problems posting this for some reason...


Declare @sDate datetime, @eDate datetime
SELECT @sDate = '1900-'+
CASE WHEN len(@sMon)=1 THEN '0' ELSE '' END + @sMon + '-' +
CASE WHEN len(@sDay)=1 THEN '0' ELSE '' END + @sDay,
@eDate = '1900-'+
CASE WHEN len(@eMon)=1 THEN '0' ELSE '' END + @eMon + '-' +
CASE WHEN len(@eDay)=1 THEN '0' ELSE '' END + @eDay,
@bDate = right(left(convert(varchar(25), @bDate, 111),11),5)+ '/1900'
SElect @sDate StartDate, @eDate EndDate, @bDate BirthDay

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Qik3Coder]

All together now.

DECLARE @_sMon varchar(2), @_sDay varchar(2), @_eMon varchar(2), @_eDay varchar(2), @_bDate datetime
SELECT @_sMon ='04', @_sDay='30', @_eMon='05', @_eDay='31', , @_bDate ='2011-05-18'
SELECT @_sMon ='12', @_sDay='15', @_eMon='01', @_eDay='10', @_bDate ='2011-12-31'
SELECT @_sMon ='12', @_sDay='15', @_eMon='01', @_eDay='10', @_bDate ='2011-01-31'


Declare @_sDate datetime, @_eDate datetime
SELECT @_sDate = '1900-'+
	CASE WHEN len(@_sMon)=1 THEN '0' ELSE '' END + @_sMon + '-' +
	CASE WHEN len(@_sDay)=1 THEN '0' ELSE '' END + @_sDay,
@_eDate = '1900-'+
	CASE WHEN len(@_eMon)=1 THEN '0' ELSE '' END + @_eMon + '-' +
	CASE WHEN len(@_eDay)=1 THEN '0' ELSE '' END + @_eDay,
@_bDate = right(left(convert(varchar(25), @_bDate, 111),11),5)+ '/1900'
SElect @_sDate StartDate, @_eDate EndDate, @_bDate BirthDay

Select 'yes'
WHERE
	--Happy Check
	(@_bDate >= @_sDate AND @_bDate <= @_eDate) OR
	--Wrap Around
	(@_eDate < @_sDate AND (@_bDate >=@_sDate OR @_bDate < @_eDate))

Guess I know some of the forum variable names now...

HTH,
Lodlaiden


If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[markros]

Take a look at the following blog post that solves this problem

http://berezniker.com/content/pages/sql/microsoft-sql-server/birthday-query-ms-sql-server

PluralSight Learning Library

 

[Vi08]

Hello everyone, just want to let you know know Thank you so much for everyone help! I am getting close to what is needed. Again, thank you.