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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2002