Dates for different calendars

Question

I am working on a project which is running in 2 countries already. Everything was fine until we deployed the project in Nepal. The problem is they are not working with standard Gregorian calendar. They have their own calendar called Bikram Sanwant Calendar. Our existing database has DateTime/Date columns which I cannot use any more because few months in their calendar have 32 days which is not a valid date according to the Gregorian calendar. So we have thought of saving all the dates in 3 different columns like Day,Month and year. Not just that but we also have used inbuilt standard SQL DateTime Functions like DateAdd, DateDiff etc... So I will also have to write my own functions and change all the queries, Stored Procedures and Functions.

Does anyone know how to deal with this or have better idea about this issue?

Please give your input and suggestions.

Thanks in advance.

Do only store one date, ISO format. Have views/functions to view and modify according to Bikram Sanwant. — jarlh, Jun 02 '15 at 06:48
some programming languages provide conversion to BS dates in their libraries, you could just convert in your application, if the language you are using doesn't support it then you should make one :D — Jeremy C., Jun 02 '15 at 06:50
Thanks for your comment, but do ISO format will accept a date like 2072-08-32? — hiren soni, Jun 02 '15 at 06:50
@JeremyC. Thanks for your reply but the problem is not the front end, I know there are many tools to convert the date, the problem is about storing them in backend. — hiren soni, Jun 02 '15 at 06:52
@hirensoni you can just store them as gregorian dates (convert from what they gave in to gregorian before storing) and show them in the correct way in your application (convert back to bs when reading)? — Jeremy C., Jun 02 '15 at 06:55
@JeremyC. Thats exactly what we thought when this issue came up. But trust me it wasn't that easy. We have so many calculations and reports on Month base. For example currently a user can ask for a report for the 3rd month which is 01-03 to 31-03 for gregorian calendar but for BS it is half jun and half july. So the only way (I think) is to store dates in BS format. — hiren soni, Jun 02 '15 at 07:01
Then you will probably have to store your dates as strings (!!!which I would strongly dissuade you to do!!!) because I don't know of any rdbms that supports this type of dates — Jeremy C., Jun 02 '15 at 07:03
also why would it be an issue to calculate a report on month base? If it's for non bikram time, just use the dates in the tables as is, if it's for Bikram let the user check a box or something, calculate the first date of the month into UTC and the last day and just alter your sql query dynamically so it uses those dates — Jeremy C., Jun 02 '15 at 07:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/79390/discussion-between-hiren-soni-and-jeremy-c). — hiren soni, Jun 02 '15 at 07:11
2072-08-32 is not a valid ISO date. Is it a Gregorian calendar date? I guess not, so don't store that value '2072-08-32', convert to Gregorian first!!! One column, no data inconsistency!!! — jarlh, Jun 02 '15 at 07:25

score 0 · Answer 1 · edited Aug 17 '16 at 14:43

Check this php code to convert AD to BS. Hope this will help you in understanding more.

<?php 
    //The Vikram Samvat calendar is 56 years 8 months and 14 days ahead (in count) of the solar Gregorian calendar
    //date_default_timezone_set('Asia/Kathmandu');

    $date = date('Y-m-d');
    $ts1 = strtotime($date);
    $year1 = date('Y', $ts1) + 56;
    $month1 = date('m', $ts1) + 8;
    $day1 = date('d', $ts1) + 14;

    if($month1 >12) {
      $year1 = $year1+1;
      //$remMonth = $month1-12;
      $month1 = $month1-12;
    }

    if($day1 >30) {
       $month1 = $month1+1;
      //$remMonth = $month1-12;
       $day1 = $day1-30;
    }

    //$day1 = date('d', $ts1);
    echo $year1."-".$month1."-".$day1;

?>

Dates for different calendars

1 Answers1