Convert Date query

[AnnieRider]

Hi

I want to select data entered in the last full month.
I have set up the following as a seperate dataset

select dateadd(mm,datediff(mm,0,GetDate())-1,0) [FirstDate],
dateadd(dd,-1,dateadd(mm,datediff(mm,0,GetDate()),0)) [LastDate]

I have then created parameters Start and End and have set Default Queried values to those returned in this dataset.

In my main query I want to select records where DateStart is between @Start and @End I have used

WHERE(dbo.CR_HISTORY.HistoryType_Ref = 'H') AND (dbo.CR_HISTORY.UserFlag3 = 1) AND dbo.CR_HISTORY.DateStart > @Start) AND (dbo.CR_HISTORY.DateStart < @End)

But I get the error

arithmetic overflow error converting expression to data type smalldatetime

I (think I) know that the issue is that the dataset is returning the date in the wrong format but I don't know how or where to fix this.

Thanks for reading
Annie

 

[xlbo]

Date conversion can be messy - not sure this will work but may be worth a quick go:

WHERE(dbo.CR_HISTORY.HistoryType_Ref = 'H') AND (dbo.CR_HISTORY.UserFlag3 = 1) AND dbo.CR_HISTORY.DateStart > cast(@Start as smalldatetime)) AND (dbo.CR_HISTORY.DateStart < cast(@End as smalldatetime))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Nesy123456789]

Can you confirm the report parameters are datatype datetime, and the datatype of CR_HISTORY.DateStart column ?

 

[DaveInIowa]

Your query's LastDate value returns '2010-01-31 00:00:00.000'; what about those records with a DateStart in the 24 hours between that and '2010-02-01 00:00:00.000'? I really think you want the first day of the current month as your @End(ing) parameter value as any value less than that will still only be in the previous month.

Why use a dataset to populate the default values when you could do that with expressions:

' For @Start
=DateSerial(Year(DateAdd("m", -1, Today)), Month(DateAdd("m", -1, Today)), 1)
' For @End
=DateSerial(Year(Today), Month(Today), 1)

Also, as Nesy mentioned, are your parameters defined as Date/Time Data types?