Q

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!


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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close