Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.