Pro Information

اشتراک گذاری اطلاعات

Professional Information

اشتراک گذاری اطلاعات

تابع تبدیل تاریخ از میلادی به شمسی Miladi to Shamsi Function in Sql

بسیاری از مواقع نیاز داریم تاریخ سیستم که میلادی است را به تاریخ شمسی تبدیل نماییم . در این پست تابع تبدیل این کار قرار داده شده . نمونه استفاده از تابع در چند سطر اول کد آورده شده

Start of Code :

USE [DbName]
GO
/****** Object: UserDefinedFunction [dbo].[MiladiTOShamsi] Script Date: 7/19/2017 10:44:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
select dbo.MiladiTOShamsi(getdate())
or
select dbo.MiladiTOShamsi(CONVERT(VARCHAR(10), GETDATE(), 102)) AS [YYYY.MM.DD]
or
select dbo.MiladiTOShamsi('1970/05/05')
*/
ALTER FUNCTION [dbo].[MiladiTOShamsi] (@MDate DateTime)
RETURNS Varchar(10)
AS
BEGIN
DECLARE @SYear as Integer
DECLARE @SMonth as Integer
DECLARE @SDay as Integer
DECLARE @AllDays as float
DECLARE @ShiftDays as float
DECLARE @OneYear as float
DECLARE @LeftDays as float
DECLARE @YearDay as Integer
DECLARE @Farsi_Date as Varchar(100)
SET @MDate=@MDate-CONVERT(char,@MDate,114)
SET @ShiftDays=466699 +2
SET @OneYear= 365.24199
SET @SYear = 0
SET @SMonth = 0
SET @SDay = 0
SET @AllDays = CAst(@Mdate as Real)
SET @AllDays = @AllDays + @ShiftDays
SET @SYear = (@AllDays / @OneYear) --trunc
SET @LeftDays = @AllDays - @SYear * @OneYear
if (@LeftDays < 0.5)
begin
SET @SYear=@SYear+1
SET @LeftDays = @AllDays - @SYear * @OneYear
end;
SET @YearDay = @LeftDays --trunc
if (@LeftDays - @YearDay) >= 0.5
SET @YearDay=@YearDay+1
if ((@YearDay / 31) > 6 )
begin
SET @SMonth = 6
SET @YearDay=@YearDay-(6 * 31)
SET @SMonth= @SMonth+( @YearDay / 30)
if (@YearDay % 30) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 7) * 30)
end
else
begin
SET @SMonth = @YearDay / 31
if (@YearDay % 31) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 1) * 31)
end
SET @SDay = @YearDay
SET @SYear=@SYear+1
--SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + CAST (@SMonth as VarChar(10)) + '/' + CAST (@SDay as VarChar(10))
SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + ltrim(right('00'+CAST (@SMonth as VarChar(10)),(2))) + '/' + ltrim(right('00'+CAST (@SDay as VarChar(10)),(2)))
Return @Farsi_Date
END

End of Code

فانکشن بالا تست شده است
برچسب ها : SQL Server اس‌کیوال سرور - سی‌کوال سرور - Structured Query Language

ADS

ADS

2021 © کلیه حقوق مادی و معنوی این سایت متعلق به آقای امیر کیانی گهر می باشد