How to extract records from the prior month (May activity) relative to the current date (if it were for ex: June 1, 2007) ?

zimmer9
zimmer9 used Ask the Experts™
on
I'm developing an Access application with Access as the front end and using SQL Server as the back end database.

I am accessing  an Access table named tblAccounts with a field titled DateLost defined as varchar(30). It is comprised of dates in the format mm/dd/ccyy (for ex: 05/25/2007).

How would you extract records from this table named tblAccounts that are in the range
of dates within the prior month ?

In other words, if I  run the report in June 2007, I want to extract records from ONLY the month
of May 2007 (05/01/2007 through 05/31//2007) ?  

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Using a string field will slow down everything, as no indexes can be used.

SELECT ...
WHERE CDate(DateLost)
    Between DateSerial(Year(Date()), Month(Date())-1,1)
    And DateSerial(Year(Date()), Month(Date()), 0)

Of course, the result will be wrong if the user uses non-US date settings!
(°v°)
Perhaps this uses the index, at least partially...

WHERE DateLost Like Format(Date()-Day(Date()), 'mm\/\*\/yyyy')

(°v°)

Author

Commented:
I get the following message using the SQL Query Analyzer:

Server: Msg 195, Level 15, State 10, Line 62
'CDate' is not a recognized function name.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
Another error is as follows:

Server: Msg 195, Level 15, State 10, Line 62
'Date' is not a recognized function name.
Most Valuable Expert 2015

Commented:
Why is the column type varchar?  If the value is a date the column type should be date/time.

/*
declare @LastDayPrevMonth datetime
declare @FirstDayPrevMonth datetime
set @LastDayPrevMonth = cast(convert(varchar(8), getdate(), 112) as datetime) - day(getdate())
set @FirstDayPrevMonth = @LastDayPrevMonth - day(@LastDayPrevMonth)+ 1
*/

--- assumes DateLost is a date/time column
WHERE DateLost BETWEEN @FirstDayPrevMonth AND @LastDayPrevMonth




Ah, SQL Server...

Date and Time Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186724.aspx

CDate() --> CAST, e.g.: CAST(DateLost AS smalldatetime)
Date() --> GETDATE()
DateSerial() --> no idea...
Format() --> ?

I'm not a T-SQL expert...
(°v°)
thanks, agx (^v°)
Most Valuable Expert 2015

Commented:
I'm not an Access expert either but I think

Convert() is close to Format()
GetDate()  is more like Now()    (date and time)



Most Valuable Expert 2015

Commented:
So between the two of us we might have it ;-) ?
right! (^v°)

Author

Commented:
How would you tie all the pieces you notedl together in one SQL statement ?
Doesn't agx's sample work? Perhaps you need to cast your field to date/time (improvising from his sample)

[... as above ...]
WHERE case(DateLost as datetime)
BETWEEN @FirstDayPrevMonth AND @LastDayPrevMonth

(°v°)

Author

Commented:
The format of the DateLost field I am working with is MM/DD/CCYY (for ex: 05/25/2007).

The value for @FirstDayPrevMonth and @LastDayPrevMonth in agx's sample work is like the following:
2007-04-30 00:00:00.000    (CCYY-MM-DD H:M:S)

This is only their default string representation (in a format without any ambiguity, you will notice). Internally, all dates are stored as a number of days offset from the 1st of Jan 1900. Hopefully, Cast() will accept your US dates and convert them as well. Once everything is in "datetime" format, comparisons will work fine.

Have you tried?
(°v°)

Author

Commented:
WHERE case(DateLost as datetime)

Server: Msg 156, Level 15, State 1, Line 71
Incorrect syntax near the keyword 'as'.
Most Valuable Expert 2015
Commented:
@zimmer9,

As harfang mentioned, the internal representation of datetime values is different.  If your column wasn't a varchar,  the date comparisons would work correctly.  I can only suggest that you save yourself some headaches and change the column type to datetime or smalldatetime :)  Although you can use cast or convert on the column, there are several disadvantages to doing so.  Its more error prone and using functions on the column can discourage or prevent the optimizer from selecting an optimal query plan.  

--- for datetime column
WHERE DateLost  BETWEEN
      cast(convert(varchar(6), dateadd(m, -1, getdate()), 112) +'01' as datetime)
      cast(convert(varchar(8), getdate(), 112) as datetime) - day(getdate())

--- for varchar column
WHERE convert(datetime, DateLost, 101) BETWEEN
      cast(convert(varchar(6), dateadd(m, -1, getdate()), 112) +'01' as datetime)
      cast(convert(varchar(8), getdate(), 112) as datetime) - day(getdate())
I'm glad you got it to work! And sorry about my typo ('case' for 'cast').
(°v°)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial