How to save not latin datetimes?

Question

How to save not latin datetimes?

Ach-230297

Right there with Babe

Points: 759
More actions
July 25, 2005 at 12:26 am

#168075

Hi there,
Is there anybody knows how can I save not latin datetimes in sql server? I mean for example: Jalali(shamsi) calender or Arabic Calender(Hijri) instead of using Julian Calendar.
Currently we do this by saving this dates in varchar type and another equivalent Latin datetime.We perform operations on Latin and then manipulate the varchar column and ofcourse application always will see this varchar column.
-Thanks alot

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 1

What you have to know is that SQLServer is storing datetime values as a kind of float. The integer part is the number of day since January 1, 1753 (for datetime datatype) and the decimal part is the milliseconds since midnight.

So what I would do in your case is to create a function which calculates your specific date out of the Julian one or you can create a calculated column based on the datetime column with your specific transformation.

You can even materialize the calculated column by adding an index on it.

Or if this is just a question of the presentation then you can use the format 130 or 131 from the convert function:

select convert(varchar, MyDateCol, 131) as MyConvertedDate from MyTable

Here is a comment from the BOL:

****Hijri is a calendar system with several variations, of which Microsoft® SQL Server™ 2000 uses the Kuwaiti algorithm.

Bye
Gabor

Ach-230297 Right there with Babe Points: 759 More actions · Answer 2

Thanks Gabor Nyul,

Let me explain the way I know in Oracle:It supports at least 5 other calendar formats such as Persian,Arabic Hijri,English Hijri,Japanes Imperial,ROC in addition to Gregorian calendar.You can do operations on dates and then return result in the proper format for your application.There is no need to other calculated column or any convertor function by you.

I want to know Can SQL Server do this?I mean in a natural and built-in method.

-Thanks

sreehari-324573 SSC Rookie Points: 27 More actions · Answer 3

hi ach could u please explain me how to save persian or jalali dates in oracle

Thanks and regards

Ach-230297 Right there with Babe Points: 759 More actions · Answer 4

Hi,

this is a session level operation in Oracle:

ALTER SESSION SET NLS_CALENDAR='PERSIAN'

/

And that is it Refer to globalization document at http://tahiti.oracle.com for more complete information.

-Regards