Query برای تبدیل تاریخ میلادی به شمسی

[pishraft]

سلام
برای تبدیل تاریخ میلادی به شمسی در SQL آیا Query هست؟
مرسی

[ASKaffash]

سلام
نه باید ازUDF استفاده کنید

[pishraft]

مرسی
Query هست که تاریخ جاری سیستم رو نشون بده؟

[SReza1]

اين هم تابع براي تبديل ميلادي به شمسي :






CREATE FUNCTION [dbo].[udf_MiladiTOShamsi] (@MDate DateTime , @Rev Char(1))
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)
DECLARE @FMonth char(2)
DECLARE @FDay char(2)
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 @FMonth = CAST(@SMonth as varchar(2))
if @SMonth < 10
set @FMonth = '0' + @FMonth

set @FDay = CAST(@SDay as varchar(2))
if @SDay < 10
set @FDay = '0' + @FDay


if @Rev = '0'
SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + @FMonth + '/' + CAST (@FDay as VarChar(10))
else
SET @Farsi_Date = CAST (@FDay as VarChar(10)) + '/' + @FMonth + '/' + CAST (@SYear as VarChar(10))
Return @Farsi_Date



END





و اينم براي تبديل شمسي به ميلادي :




CREATE FUNCTION [dbo].[udf_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 @i = 0
select @DateStr = dbo.InsertSlashTarikh(@DateStr)
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.udf_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



اينم كد براي بدست آوردن تاريخ سيستم . ضمن اينكه با تابع Datepart ميتوني قسمتهاي مختلف روز ، ماه ، سال ، ساعت را بياري بيرون:


SELECT GETDATE()

[ASKaffash]

سلام


Select Convert(DateTime,GetDate(),21)