Representing a date having 32 as the maximum value for the Day

Question

I am creating an application in asp.net using Sql server 2005. In my application I have to represent many dates and dates from Nepal (Bikram sambhat), in which the maximum day for some month can be 32.

So, what is the best option for presenting dates on sql server so that 32 can be placed per day, and can this be easily compared (processed) on sql server as well as in asp.net?

Answer 1

There is no easy transition from Nepalese to Gregorian. There are two ways to think about it:

• need to show the date of Nepalese
• need to be manipulated (for example, to find the difference between months, days, etc.).

The first one is simple - store as varchar
The second is not. If you want to know the difference between the two dates in Nepal, you can save the 2nd date as an equivalent in Gregorian font and use the datif / date (day) between them. However, the dateadd (month) will be useless for you here if you do not want to know the difference in 2 Nepalese dates in Gregorian date months - not often.

It seems that just storing in varchar and having a library for Nepalese dates, either in the interface or in the CLR, would be better.

---

This can help conversions have a fully materialized table of events in Nepal with a corresponding Gregorian date, so the layout will be

 NepaliYear NepaliMonth NepaliDay Gregorian x 4 2 2014-21-23 etc 

But I'm not sure if that would help many (Nepalese) date mathematicians go beyond conversion — and only if you need such a conversion in SQL Server.

EDIT

Comment by @pst

There is a reason VARCHAR prefers datetime. I suggested that the frequency, most of the activity - reading / writing Nepalese dates - which is stored and read as VARCHAR - there is no conversion. If and only when date math is required, it includes a library - in this case you call the conversion, and only if you need the equivalent in the Gregorian calendar at any time. If all you need is the math between NepaliDate / scalar or NepaliDate / NepaliDate - again, datetime is of no use - it cannot process day # 32 per month .

Answer 2

There is a guy who implemented some classes for converting between dates of the Nepalese period and Gregorian dates. That way, you can enter dates in Nepali format, but store them in a format that SQL Server understands. See here: http://rrajbhandari.blogspot.com/2010/06/bikram-sambat-classes-and-controls.html

Remember that the date in the calendar can be converted to another calendar - they indicate the same day.

Answer 3

I would recommend using the built-in date type. It looks like any date in your format can easily be converted to the standard sql format.

I would just write a utility to convert any date from the sql server to your display format whenever you load or write to the database.

Answer 4

Check out the Nepali calendar , this may help.