Is it possible to change the datatype Date in Sql Server

Question

I live in Iran and we here in Iran use Solar dates instead of the Gregorian dates. Is it possible to change Date datatype from Gregorian date to Solar date. Also, I want to use the date function such as DateAdd() , DateDiff() , .. in my query.

EDIT :

Months desciption in solar date:

1-  31 day
2-  31 day
3-  31 day
4-  31 day
5-  31 day
6-  31 day
7-  30 day
8-  30 day
9-  30 day
10- 30 day
11- 30 day
12- 29 day (in Leap year 30 day)
SUM-365 day in each year

and start of solar date is 23-3-... of each Gregorian date.

EDIT 1

I use below function in SQL Server for convert gregorian date to solar date:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[PersianDate]
(@MiladiDate DATE)
returns varchar(10)
AS
BEGIN
    Declare @days int
    Declare @year int
    set @year=Year(@MiladiDate) 
    Declare @month int  
    Set @days=DATEDIFF ( day ,  Convert(Date,cast(Year(@MiladiDate) as varchar)+'/1/1'), @MiladiDate)+1
    if @days>79  --یعنی یک فروردین ماه
    begin
        set @days=@days-79
        if @days<=186
        begin
            set @month=@days/31;
            if (@days%31=0)
                set @days=31
            else
            begin
                set @month=@month+1
                set @days=@days%31
            end
        end
        else
        begin
            set @days=@days-186
            set @month=6+@days/30;
            if (@days%30=0)
                set @days=30
            else
            begin
                set @month=@month+1
                set @days=@days%30
            end
        end
        set @year=@Year-621
    end
    else
    begin
        set @year=@year-1   
        if (((@Year % 100)<> 0 and (@Year % 4) = 0) or ((@Year % 100)= 0 and (@Year % 400) = 0))
            set @days=@days+11
        else
            set @days=@days+10
        set @month=@days/30+9
        if (@days%30=0)
            set @days=30
        else
        begin
            set @month=@month+1
            set @days=@days%30
        end
        set @year=@year-621
    end
    RETURN cast (@year as varchar)+'/'+ 
                                    case
                                    when @month<10 then '0'
                                    else ''
                                    END
                                    + cast (@month as varchar)+'/'+
                                    case 
                                    when @days<10 then '0'
                                    else ''
                                    END
                                    + cast (@days as varchar)
END

EDIT 2

For convert solar date to gregorian date can use CLR function:

Create CLR Function

The Date datetype is, internally, an integer. It's default representation may vary, but there's no reason to alter the actual type. — Damien_The_Unbeliever, Jun 08 '12 at 13:17

score 2 · Answer 1 · answered Jun 08 '12 at 12:26

I would suggest that you build a calendar table that has Gregorian dates and solar dates as two columns (and other columns as well, such as the day of the week, the month number, the year, the name of the month). You should be able to build such a table easily in Excel and then import it into a table.

Then, you can take one of two approaches. The first is to store all the dates as Gregorian dates, and then look up your date values to convert on input and output. You would do this if you wanted to use a lot of the built-in functionality for dates.

The second approach is to store the dates as a string representing the solar date. Then, you can use the table to convert to Gregorian date when you want to do built-in operations. However, you might find that just having a calendar table makes it unnecessary to use most of the built-in functions.

Diego · Answer 2 · 2012-06-08T12:23:14.410

0

you need to use sp_configure to set the default date format of your server. http://support.microsoft.com/kb/173907

edited Jun 08 '12 at 12:23

answered Jun 08 '12 at 12:07

Diego

34,802
21
91
134

I want to change Date data type (no change date display format) only in one database. – mehdi lotfi Jun 08 '12 at 12:12

score 0 · Accepted Answer · answered Jun 08 '12 at 12:41

0

You cannot change the Date datatype calender from gregorian to solar.

answered Jun 08 '12 at 12:41

Asif

2,657
19
25

score 0 · Answer 4 · edited May 23 '17 at 12:14

0

I find answare of my question. I must use gregorian date data type in sql server and convert date columns in my application to solar date. My application write with C# and below link help to convert data from gregorian date to solar date. Display gregoriandate in solar date format

Thanks all.

edited May 23 '17 at 12:14

Community

1
1

answered Jun 08 '12 at 12:58

mehdi lotfi

11,194
18
82
128

Is it possible to change the datatype Date in Sql Server

4 Answers4

Linked