Ask the Expert

Date calculation in MS Access

I have a subscriber table in which the startdate of the subscription is recorded. Members can subscribe at any time, but all subscriptions expire on 1 March.

In a query, I split the [StartDate] into 3 columns (Day, Month and Year) using e.g. Year([StartDate]).

I have used an Iif calculation to determine in which year the subscription should lapse. The logic is that if [StartMonth]<3, then [EndYear]=[StartYear], otherwise [EndYear]=[StartYear]+1.

This works fine, but now I need to generate the end date as [EndDate]=1/3/[EndYear]. I can't work out how format this. I keep getting the right display, but not as a date format. This means that I cannot sort by date, etc. Help!


    Requires Free Membership to View

Since you're using the Iif function, this is probably MS Access. In that case, you'll want to use the DateSerial function. DateSerial creates date datatypes, given numeric values for year, month, and day.

select StartDate
     , Iif( Month(StartDate) < 3
            , DateSerial(Year(Startdate),3,1)
            , DateSerial(Year(Startdate)+1,3,1)
          ) as EndDate
  from Subscriptions
order by StartDate

I tested this in Access 97, and it works fine.

In standard SQL, you'd use something like --

select StartDate
     , CASE
        WHEN Month(StartDate) < 3
          THEN CAST( CAST(Year(Startdate) AS CHAR(4))
                      ||'-03-01' AS DATE)
          ELSE CAST( CAST(Year(Startdate)+1 AS CHAR(4))
                      ||'-03-01' AS DATE)
       END as EndDate
  from Subscriptions
order by StartDate

Caution: I did not test this; I've never actually run CAST AS DATE syntax. Every database has its own date functions, which I always use instead.

For More Information


This was first published in June 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: