是否可以更改 Sql Server 中的数据类型日期 [英] Is it possible to change the datatype Date in Sql Server

问题描述

我住在伊朗，我们在伊朗这里使用阳历而不是公历.是否可以将 Date 数据类型从公历日期更改为太阳日期.另外，我想在查询中使用 DateAdd() 、 DateDiff() 、 .. 等日期函数.

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.

太阳日期的月份描述:

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

太阳日期的开始是每个公历日期的 23-3-....

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

编辑 1

我在 SQL Server 中使用以下函数将公历日期转换为太阳日期:

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

编辑 2

为了将太阳日期转换为公历日期可以使用 CLR 函数:

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

创建 CLR 函数

推荐答案

您不能将 Date 数据类型日历从 gregorian 更改为 Solar.

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

这篇关于是否可以更改 Sql Server 中的数据类型日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！