如何在SQL Server中的波斯语中转换日期时间 [英] How to convert datetime in persian in sql server
问题描述
我想在SQL Server中将日期时间转换为波斯日期时间。我的日期时间是MM / DD / YYYY格式。在sql server中是否有任何功能可以执行此操作,因为我想使用此日期
I want to convert my datetime into persian datetime in sql server. My datetime is in MM/DD/YYYY format. Is there any function in sql server to do this as when i want hijri datetime i use this
select CONVERT(VARCHAR(40), getdate(), 131) //Output is 14/08/1432 5:02:01:197PM
我正在使用sql server 2008
I'm using sql server 2008
推荐答案
我知道回答这个问题为时已晚,但是我已经提交了我长时间使用的函数,没有任何错误,我见过的所有其他方法在插入年代都存在问题:
I know it is too late for answering this question, but I've submitted the function that I'm using for a long time without any bug, all of other methods which I've ever seen have problem with intercalary years:
CREATE FUNCTION [CalculatePersianDate] ( @intDate DATETIME )
RETURNS NVARCHAR(max)
BEGIN
DECLARE @shYear AS INT ,@shMonth AS INT ,@shDay AS INT ,@intYY AS INT ,@intMM AS INT ,@intDD AS INT ,@Kabiseh1 AS INT ,@Kabiseh2 AS INT ,@d1 AS INT ,@m1 AS INT, @shMaah AS NVARCHAR(max),@shRooz AS NVARCHAR(max),@DayCnt AS INT
DECLARE @DayDate AS NVARCHAR(max)
SET @intYY = DATEPART(yyyy, @intDate)
IF @intYY < 1000 SET @intYY = @intYY + 2000
SET @intMM = MONTH(@intDate)
SET @intDD = DAY(@intDate)
SET @shYear = @intYY - 622
SET @DayCnt = 5
IF ( ( @intYY - 1992 ) % 4 = 0) SET @Kabiseh1 = 0 ELSE SET @Kabiseh1 = 1
IF ( ( @shYear - 1371 ) % 4 = 0) SET @Kabiseh2 = 0 ELSE SET @Kabiseh2 = 1
SET @m1 = 1
SET @d1 = 1
SET @shMonth = 10
SET @shDay = 11
IF ( ( @intYY - 1993 ) % 4 = 0 ) SET @shDay = 12
WHILE ( @m1 != @intMM ) OR ( @d1 != @intDD )
BEGIN
SET @d1 = @d1 + 1
SET @DayCnt = @DayCnt + 1
IF
(@d1 = 32 AND (@m1 = 1 OR @m1 = 3 OR @m1 = 5 OR @m1 = 7 OR @m1 = 8 OR @m1 = 10 OR @m1 = 12))
OR
(@d1 = 31 AND (@m1 = 4 OR @m1 = 6 OR @m1 = 9 OR @m1 = 11))
OR
(@d1 = 30 AND @m1 = 2 AND @Kabiseh1 = 1)
OR
(@d1 = 29 AND @m1 = 2 AND @Kabiseh1 = 0)
BEGIN
SET @m1 = @m1 + 1
SET @d1 = 1
END
IF @m1 > 12
BEGIN
SET @intYY = @intYY + 1
SET @m1 = 1
END
IF @DayCnt > 7 SET @DayCnt = 1
SET @shDay = @shDay + 1
IF
(@shDay = 32 AND @shMonth < 7)
OR
(@shDay = 31 AND @shMonth > 6 AND @shMonth < 12)
OR
(@shDay = 31 AND @shMonth = 12 AND @Kabiseh2 = 1)
OR
(@shDay = 30 AND @shMonth = 12 AND @Kabiseh2 = 0)
BEGIN
SET @shMonth = @shMonth + 1
SET @shDay = 1
END
IF @shMonth > 12
BEGIN
SET @shYear = @shYear + 1
SET @shMonth = 1
END
END
IF @shMonth=1 SET @shMaah=N'فروردین'
IF @shMonth=2 SET @shMaah=N'اردیبهشت'
IF @shMonth=3 SET @shMaah=N'خرداد'
IF @shMonth=4 SET @shMaah=N'تیر'
IF @shMonth=5 SET @shMaah=N'مرداد'
IF @shMonth=6 SET @shMaah=N'شهریور'
IF @shMonth=7 SET @shMaah=N'مهر'
IF @shMonth=8 SET @shMaah=N'آبان'
IF @shMonth=9 SET @shMaah=N'آذر'
IF @shMonth=10 SET @shMaah=N'دی'
IF @shMonth=11 SET @shMaah=N'بهمن'
IF @shMonth=12 SET @shMaah=N'اسفند'
IF @DayCnt=1 SET @shRooz=N'شنبه'
IF @DayCnt=2 SET @shRooz=N'یکشنبه'
IF @DayCnt=3 SET @shRooz=N'دوشنبه'
IF @DayCnt=4 SET @shRooz=N'سهشنبه'
IF @DayCnt=5 SET @shRooz=N'چهارشنبه'
IF @DayCnt=6 SET @shRooz=N'پنجشنبه'
IF @DayCnt=7 SET @shRooz=N'جمعه'
--SET @DayDate = @shRooz + " " + LTRIM(STR(@shDay,2)) + " " + @shMaah + " " + STR(@shYear,4)
--پنجشنبه 17 اردیبهشت 1394
/*
SET @DayDate = LTRIM(STR(@shDay,2)) + " " + @shMaah + " " + STR(@shYear,4)
--17 اردیبهشت 1394
SET @DayDate = STR(@shYear,4) + "/"+LTRIM(STR(@shMonth,2)) + "/" + LTRIM(STR(@shDay,2))
--1394/2/17
--1394/02/17
*/
SET @DayDate = REPLACE(RIGHT(STR(@shYear, 4), 4), ' ', '0') + '/'+ REPLACE(STR(@shMonth, 2), ' ', '0') + '/' + REPLACE(( STR(@shDay,2) ), ' ', '0')
RETURN @DayDate
END
自定义函数结果非常容易。
来自以下位置:此页面
It is really easy to customize the result of the function. adopted from: this page
这篇关于如何在SQL Server中的波斯语中转换日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!