Q
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.

#### Have a question for an expert?

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.

## SearchDataManagement

• ### InfluxDB Cloud 2.0 expands open source time series database

While the core open source InfluxDB 2.0 project is still in alpha, InfluxData has advanced its cloud service to support new ...

• ### New cloud and on-premises options for Oracle Autonomous Database

Oracle introduced new cloud and on-premises deployment options for its namesake database as the tech vendor’s Oracle OpenWorld ...

• ### How big tech breakup would affect the tech industry

Breaking up the tech giants would be difficult and might create initial chaos in market, help smaller competitors and potentially...

• ### Oracle BI platform on the comeback trail

Time had seemingly left Oracle's business intelligence tools behind -- until the vendor responded by consolidating its BI ...

• ### BI for mobile remains a challenge for vendors

While some BI vendors have developed effective mobile apps that provide concise insights, those that have attempted to recreate ...

• ### Magento BI update a benefit to vendor's e-commerce customers

Magento rolled out the Magento Business Intelligence Summer 2019 Release, updating its BI platform with enhanced scheduling ...

## SearchSAP

• ### Navy sails SAP ERP systems to AWS GovCloud

The Navy consolidated 26 various ERP systems onto SAP HANA on the AWS GovCloud, leading to data reduction, lower TCO, and setting...

• ### SAP HANA application collects concussion data for real-time analysis

Protecht, an SAP HANA based system, enables teams in contact sports to measure and analyze data about hits though an IoT sensor ...

• ### S/4HANA public cloud vs. private cloud: Compare the pros, cons

In addition to SAP's on-premises and hybrid models, the vendor offers S/4HANA in public and private clouds. Here's a look at the ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Cloud marketplace as a service creates new dev possibilities

A neutral cloud marketplace would allow developers to pick and choose applications, without the constraints of one cloud. What ...

• ### No doubt now about Java release cycle

At the Oracle Code One keynote, the recent track record of on-time releases and feature enhancements is a topic to boast about.

• ### Java SE 13 adds performance, security, stability features

Java SE 13 provides developers with new features to make the Java language and platform more secure and stable. Oracle has also ...

## SearchDataCenter

• ### New Dell EPYC servers embrace AMD Rome chips

Dell EMC goes from Naples to Rome with a new line of EPYC servers, including Ready Solutions for high-performance computing and ...

• ### Can next-gen SIEM help cybersecurity initiatives?

More organizations are using SIEM, AI and cloud technology to minimize security breaches. Though despite interest, this ...

• ### IBM z15 mainframe secures data across multi-cloud environments

IBM unveiled the latest in its line of mainframes capable of processing 1 trillion web transactions a day. The IBM z15 ...

## SearchContentManagement

• ### Cohesion is latest Acquia acquisition

While Acquia would not provide specifics of the deal, the decision behind the acquisition of Cohesion was the chance to bring ...

• ### 3 things to learn before considering blockchain integration

Paul Swider, CTO at RealActivity LLC, discusses how to use blockchain technology to increase the security and usefulness of ...

• ### Microsoft PowerApps pricing proposal puts users on edge

Microsoft's PowerApps pricing changes, expected to take effect in October, could make them too expensive for some users who ...

## SearchHRSoftware

• ### HiQ Labs vs LinkedIn case OKs robot monitoring of employees

A U.S. Court of Appeals ruled on the LinkedIn vs HiQ lawsuit, which could help shape how services gather information on social ...

• ### MyPayrollHR collapse stirs allegations, questions, anger

The problems created by the closing of MyPayrollHR may take a long time to resolve. The FBI has been contacted and lawsuits are ...

• ### Before deploying recruitment chatbots, define use cases

HR departments can benefit from using chatbots for basic tasks, but the technology will eventually be able to handle more complex...

Close