تبدیل تاریخ در SQL Server

در نسخه های +2012 از دستور فوق می توانید استفاده نمایید :

select format(GETDATE(),'yyyy-MM-dd hh:mm:ss','fa-ir')

در نسخه های قدیمی تر از متد های زیر استفاده نمایید

جهت تبدیل تاریخ شمسی به میلادی از کد ذیل استفاده نمائید :

CREATE FUNCTION [dbo].[ShamsiToMiladi](@DateStr varchar(10))
RETURNS DATETIME
AS 
BEGIN
   declare @YYear int
   declare @MMonth int
   declare @DDay int
   declare @epbase int
   declare @epyear int
   declare @mdays int
   declare @persian_jdn int
   declare @i int
   declare @j int
   declare @l int
   declare @n int
   declare @TMPRESULT varchar(10)
   declare @IsValideDate int
   declare @TempStr varchar(20)
   DECLARE @TmpDateStr varchar(10)
   
   SET @i=charindex('/',@DateStr)
   
   IF LEN(@DateStr) - CHARINDEX('/', @DateStr,CHARINDEX('/', @DateStr,1)+1) = 4
   BEGIN
     SET @TmpDateStr = dbo.ReversDate(@DateStr)
     IF ( ISDATE(@TmpDateStr) =1 )  
       RETURN @TmpDateStr
     ELSE
        RETURN NULL
   END
   ELSE
     SET @TmpDateStr = @DateStr
   
   IF ((@i<>0) and
       (dbo.SubStrCount('/', @TmpDateStr)=2) and
        (ISNUMERIC(REPLACE(@TmpDateStr,'/',''))=1) and
        (charindex('.',@TmpDateStr)=0)
       )
   BEGIN
       SET @YYear=CAST(SUBSTRING(@TmpDateStr,1,@i-1) AS INT)
                IF ( @YYear< 1300 )
                      SET @YYear =@YYear + 1300
                IF @YYear > 9999
                  RETURN NULL
   
       SET @TempStr= SUBSTRING(@TmpDateStr,@i+1,Len(@TmpDateStr))
   
       SET @i=charindex('/',@TempStr)
       SET @MMonth=CAST(SUBSTRING(@TempStr,1,@i-1) AS INT)
       SET @MMonth=@MMonth-- -1
   
       SET @TempStr= SUBSTRING(@TempStr,@i+1,Len(@TempStr))   
   
       SET @DDay=CAST(@TempStr AS INT)
       SET @DDay=@DDay-- - 1
   
                 IF ( @YYear >= 0 )
                     SET @epbase = @YYear - 474
                 Else
                     SET @epbase = @YYear - 473
                 SET @epyear = 474 + (@epbase % 2820)
   
        IF (@MMonth <= 7 )
                       SET @mdays = ((@MMonth) - 1) * 31
        Else
            SET @mdays = ((@MMonth) - 1) * 30 + 6
   
        SET @persian_jdn =(@DDay)  + @mdays + CAST((((@epyear * 682) - 110) / 2816) as int)  + (@epyear - 1) * 365  +  CAST((@epbase / 2820)  as int ) * 1029983  + (1948321 - 1)
   
   
   
        IF (@persian_jdn > 2299160) 
                 BEGIN
            SET @l = @persian_jdn + 68569
            SET @n = CAST(((4 * @l) / 146097) as int)
            SET @l = @l -  CAST(((146097 * @n + 3) / 4) as int)
            SET @i =  CAST(((4000 * (@l + 1)) / 1461001) as int)
            SET @l = @l - CAST( ((1461 * @i) / 4) as int) + 31
            SET @j =  CAST(((80 * @l) / 2447) as int)
            SET @DDay = @l - CAST( ((2447 * @j) / 80) as int)
             SET @l =  CAST((@j / 11) as int)
            SET @MMonth = @j + 2 - 12 * @l
            SET @YYear = 100 * (@n - 49) + @i + @l
                 END
   
           SET @TMPRESULT=Cast(@MMonth as varchar(2))+'/'+CAST(@DDay as Varchar(2))+'/'+CAST(@YYear as varchar(4))  
           RETURN Cast(@TMPRESULT as Datetime)
   
    END
    RETURN NULL     
   
END
 
CREATE  FUNCTION [dbo].[SubStrCount](@SubStr varchar(8000), @MainText Text)  
RETURNS  int
AS 
BEGIN
  DECLARE @StrCount int
  DECLARE @StrPos int
  
  SET @StrCount = 0
  SET @StrPos = 0
  SET @StrPos = CHARINDEX( @SubStr, @MainText, @StrPos)
  
  WHILE @StrPos > 0
  BEGIN
    SET @StrCount = @StrCount + 1
    SET @StrPos = CHARINDEX( @SubStr, @MainText, @StrPos+1)
  END
  
  RETURN  @StrCount  
END
 
 
CREATE FUNCTION [dbo].[ReversDate] (@DateStr varchar(10))
RETURNS varchar(10)
  
AS
  
BEGIN
  DECLARE @TempStr varchar(10)
  DECLARE @StartIndex int
  DECLARE @SubStrLen int
  DECLARE @i int
  
  SET @TempStr = ''
  SET @StartIndex = LEN(@DateStr) + 2
  SET @i = LEN(@DateStr)
  WHILE @i > 0
  BEGIN
    IF SUBSTRING(@DateStr,@i,1) IN ('/', '-')
    BEGIN
      SET @SubStrLen = @StartIndex - (@i + 2)
      SET @StartIndex = @i + 1
      SET @TempStr = @TempStr + SUBSTRING(@DateStr,@StartIndex,@SubStrLen) + SUBSTRING(@DateStr,@i,1)
    END
    SET @i = @i - 1
  END
  IF @TempStr <> ''
  BEGIN
    SET @SubStrLen = @StartIndex - 2
    SET @TempStr = @TempStr + SUBSTRING(@DateStr,1,@SubStrLen)
  END
  ELSE
    SET @TempStr = @DateStr
  RETURN @TempStr
END

جهت تبدیل تاریخ میلادی به شمسی از کد ذیل استفاده نمائید :

CREATE 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))
 Return @Farsi_Date
 
 
 
END