MM/YY only

[munchen]

I currently have a @StartDate and @EndDate parameters that prompt the user to enter dates like so 01/01/07. Here is the code below:

(
@StartDate varchar(10),
@EndDate varchar(10)
)

SELECT
tblA.ID,
tblA.Date
FROM
tblA
WHERE
(CONVERT(DATETIME, CONVERT(VARCHAR(10), tblA.Date, 103)) >= @StartDate AND CONVERT(DATETIME, CONVERT(VARCHAR(10), tblA.Date, 103)) <= @EndDate)

This all works as expected, however I now need to enter the parameters so I can enter just the month and year. Is it possible to enter either January 07 or 01/07 as the @StartDate and say March 07 or 03/07 and this would bring back all data from the 1st January to the end of March?

 

[AlexCuse]

Have a look at this:

[COLOR=blue]declare[/color] @startdate [COLOR=blue]varchar[/color](10)

[COLOR=blue]set[/color] @startdate = [COLOR=red]'03/07'[/color]
[COLOR=blue]select[/color] @startdate

[COLOR=blue]set[/color] @startdate = [COLOR=#FF00FF]left[/color](@startdate, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], @startdate) - 1) + [COLOR=red]'/01'[/color] + [COLOR=#FF00FF]right[/color](@startdate, 3)
[COLOR=blue]select[/color] @startdate

One word of advice, If possible I think you would save yourself a lot of headaches if you had the users pass in a 4 digit year, and worked with the date in this format: YYYYMMDD.

HOpe it helps,

Alex

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[munchen]

AlexCuse,

Should I change the @Startdate and @Enddate parameters to be varchar(5) as it will only ever a maximum of 03/07?

The users will need to enter both a start date and an end date and then see the results for the full months that they enter so the @Startdate will run from the 1st of the month and the @Enddate will go to the last day of the month.

How will the where clause that looks at the field tblA.Date now be shown in the where clause?

WHERE
(CONVERT(DATETIME, CONVERT(VARCHAR(10), tblA.Date, 103)) >= @StartDate AND CONVERT(DATETIME, CONVERT(VARCHAR(10), tblA.Date, 103)) <= @EndDate)

 

[gmmastros]

Here is my suggestion.

(
@StartDate [COLOR=blue]varchar[/color](10),
@EndDate [COLOR=blue]varchar[/color](10)
)

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]Declare[/color] @Start [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Declare[/color] @End [COLOR=#FF00FF]DateTime[/color]

[COLOR=blue]Set[/color] @Start = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]Left[/color](@StartDate, 2)) - 1, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]year[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=red]'20'[/color] + [COLOR=#FF00FF]Right[/color](@StartDate, 2)) - 1900, 0))
[COLOR=blue]Set[/color] @End = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]Left[/color](@EndDate, 2)), [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]year[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=red]'20'[/color] + [COLOR=#FF00FF]Right[/color](@EndDate, 2)) - 1900, 0))

[COLOR=blue]SELECT[/color]
tblA.ID, 
tblA.Date
[COLOR=blue]FROM[/color]
tblA 
[COLOR=blue]WHERE[/color] tblA.Date >= @Start 
      AND tblA.Date < @End

Essentially, we declare a couple DateTime variables and then use the parameters to create real DateTime's which we use in the where clause.

You'll notice that if you pass 03/07 in for the @EndDate, the @End variable will actually become March 1, 2007. This is OK because the where clause does a Less than operation on @End.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Correction (to the explanation)

You'll notice that if you pass 03/07 in for the @EndDate, the @End variable will actually become March 1, 2007. This is OK because the where clause does a Less than operation on @End.

If you pass 03/07, @End will get a value of '2007-04-01' ([!]April 1, 2007[/!]). This is OK because the where clause does a Less than operation on @End. Basically, @End will be 1 month greater than what was passed in, but it will always be the first of the month, so a Less Than operation will cause this to return the correct data.

I'm sorry if I confused you with my previous explanation.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom