Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Thai Buddhist Calendar format in SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 3:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2008 6:29 AM
Points: 5, Visits: 6
People in Thailand use the Thai Buddha calendar format, which is 543 years ahead of the Gregorian calendar format. The year in the Thai Buddha calendar now is 2551. This, though handled in the operating system (Windows 2003), is not handled in SQL Server 2005.

I have tried searching settings in SQL Server, but have not been able to find anything. Is there a setting where I can set the calendar/date formats at the server/database level?

Steps to reproduce the problem:

1. Change Windows regional settings to Thai.
2. Add the Thai language in the Windows regional settings.
3. Set THAI_CS_AS as the default collation during SQL Server installation.
4. Set THAI_CS_AS as the collation for the database I'm testing this on.
5. Set the default language to Thai (sp_configure 'default_language', 32)
6. Run the following commands:

===
set language Thai
go
set dateformat 'dmy'
go

DECLARE @Today DATETIME
SET @Today = '12/1/2008'
SELECT DATENAME(month, @Today) AS 'Month'
===

The output I get is this (and this is the expected output): มกราคม

However, when I try running the same query to get the date:

===
DECLARE @Today DATETIME
SET @Today = '12/1/2008'
SELECT DATENAME(year, @Today) AS 'Year'
===

I get '2003' as the output. It should be displaying 2551.

I would like to know if there is a setting where I can set the calendar format from Gregorian to Thai Buddhist at the database/server level.
Post #486239
Posted Thursday, April 17, 2008 4:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
2003? Wow - that is crazy.

I don't see how SQL Server will return anything other than 2008 when you ask it for the year of '12/1/2008'.



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #486250
Posted Thursday, April 17, 2008 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2008 6:29 AM
Points: 5, Visits: 6
Hi Ryan,

2003 was an example...

We checked this out in Oracle... it is supported by setting up a parameter nls_calendar. It returns the date as 17-04-2551 which is the current date in Bhuddist Calendar.

We wanted this Bhuddist date in the apps screens using MS SQL SERVER.

Any solution?

Post #486264
Posted Thursday, April 17, 2008 5:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
SELECT DATEADD(YEAR, 543, GETDATE())


N 56°04'39.16"
E 12°55'05.25"
Post #486288
Posted Thursday, April 17, 2008 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2008 6:29 AM
Points: 5, Visits: 6
Hi,

I wish it were as simple as adding another 543 years to the date, but there are complications involved. More on the Buddhist calendar here: http://en.wikipedia.org/wiki/Buddhist_calendar

Is there any way we can change the default calendar format from Gregorian (the current year is 2008) to Thai Buddhist calendar (the current year is 2551)?
Post #486297
Posted Thursday, April 17, 2008 5:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
What calender is the server using?

does this show the year as 2551?
Post #486301
Posted Thursday, April 17, 2008 6:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2008 6:29 AM
Points: 5, Visits: 6
When we do a mouseover on the system clock, it displays 2551, but when you double-click and open the system clock, it displays 2008 (I guess this is how it works after setting the Windows Regional Settings to Thai).
Post #486317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse