sarami
چهارشنبه 18 مرداد 1385, 23:54 عصر
ظاهرا تعداد دوستانی که به سمت اکسس پروجکت متمایل شدن زیاد شده برا همین گفتم شاید نکته هایی وجود داشته باشه که به ذهنم می رسه برای دوستان مفید باشه از اون جمله میشه به تاریخ شمسی برای sql اشاره کرد . دوستانی که با اکسس پروجکت کار میکنن این نکته رو فراموش نکنن که هیچ گاه برای استفاده از تاریخ به تاریخ سیستم سرویس گیرنده (Client) متکی نباشید و حتما تاریخ رو از سمت سذویس دهنده (Server)بخونین تا اگه تاریخ سیستم کاربری تنظیم نبود رکوردهای اشتباه وارد بانک شما نشه.
اینم sp اماده:
CREATE PROCEDURE dbo.sp_Hijri_Date
AS
DECLARE @a datetime
DECLARE @Y int,@M INT,@D INT,@YY int,@MM INT,@DD INT,@T varchar(6)
set @a=getdate()
set @Y =(Year(getdate()))
set @M = (Month(getdate()))
set @D = (Day(getdate()))
If (@M = 1 And @D < 21 )
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D + 10
End
If @M = 1 And @D > 20
BEGIN
set @YY = @Y - 622
set @MM = @M + 10
set @DD = @D - 20
End
If @M = 2 And @D < 20
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D+ 11
End
If @M = 2 And @D > 19
BEGIN
set @YY = @Y - 622
set @MM = @M + 10
set @DD = @D - 19
End
If @M = 3 And @D < 21
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D+ 9
End
If @M = 3 And @D > 20
BEGIN
set @YY = @Y- 621
set @MM = @M - 2
set @DD = @D- 20
End
If @M = 4 And @D < 21
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D+ 11
End
If @M = 4 And @D > 20
BEGIN
set @YY = @Y - 621
set @MM = @M- 2
set @DD = @D - 20
End
If @M = 5 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 10
End
If @M = 5 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If @M = 6 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 10
End
If @M = 6 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If @M = 7 And @D < 23
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 7 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 22
End
If @M = 8 And @D < 23
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 8 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 22
End
If @M = 9 And @D < 23
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 9 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 22
End
If @M = 10 And @D < 23
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 8
End
If @M = 10 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 22
End
If @M = 11 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D+ 9
End
If @M = 11 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 21
End
If @M = 12 And @D < 22
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 12 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If (Right(@Y, 2) % 4 = 0 And @M > 2)
BEGIN
set @DD = @DD+ 1
If @MM <= 6
BEGIN
If @DD > 31
BEGIN
set @DD = 1
set @MM= @MM + 1
End
else if @MM > 6
BEGIN
If @DD > 30
BEGIN
set @DD = 1
set @MM= @MM + 1
End
End
If @MM = 12 And @DD= 30
BEGIN
set @MM=1
set @dd=1
set @yy=@yy+1
End
end
End
If (Right(@Y, 2) - 1)%4 = 0 And @M <= 3
BEGIN
If Not ( @M = 3 And @D > 20)
BEGIN
set @DD= @DD + 1
If @DD = 31
BEGIN
set @DD = 1
set @MM = @MM + 1
End
End
End
SET @T = Right(str(@YY), 2) + Right('00'+(LTRIM(STR(@MM))), 2) + Right('00'+(LTRIM(STR(@DD))), 2)
select @t as 'Date'
GO
اینم فانکشن تاریخ برا کد نویستون تا بتونین از طریق اون تاریخ سرور رو در کد نویسی داشته باشین
Function Hijri_ShortDate() As String
On Error GoTo Err_Handler
Dim rst As ADODB.Recordset
Dim strsql As String
strsql = "EXECUTE sp_Hijri_Date"
Set rst = New ADODB.Recordset
rst.open strsql, Application.CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If Not rst.EOF Then
Hijri_ShortDate = rst.Fields(0)
End If
rst.Close
Exit Function
Err_Handler:
MsgBoxFa Err.Description, , "dateErr"
End Function
Function Hijri_LongDate()
On Error GoTo Err_Handler
Dim Today As Date, strWeekDay As String, strMonth As String
Today = Now
Select Case Weekday(Today)
Case 1
strWeekDay = "í˜ÔäÈå"
Case 2
strWeekDay = "ÏæÔäÈå"
Case 3
strWeekDay = "Óå ÔäÈå"
Case 4
strWeekDay = "چåÇÑÔäÈå"
Case 5
strWeekDay = "پäÌÔäÈå"
Case 6
strWeekDay = "ÌãÚå"
Case 7
strWeekDay = "ÔäÈå"
End Select
Select Case Mid(Hijri_ShortDate, 3, 2)
Case 1
strMonth = "ÝÑæÑÏíä"
Case 2
strMonth = "ÇÑÏíÈåÔÊ"
Case 3
strMonth = "ÎÑÏÇÏ"
Case 4
strMonth = "撄"
Case 5
strMonth = "ãÑÏÇÏ"
Case 6
strMonth = "ÔåÑíæÑ"
Case 7
strMonth = "ãåÑ"
Case 8
strMonth = "ÂÈÇä"
Case 9
strMonth = "ÂÐÑ"
Case 10
strMonth = "Ïí"
Case 11
strMonth = "Èåãä"
Case 12
strMonth = "ÇÓÝäÏ"
End Select
Dim yy As Integer
yy = Left(Hijri_ShortDate, 2)
Hijri_LongDate = strWeekDay & ", " & Right(Hijri_ShortDate, 2) & " " & strMonth & "," & yy
Exit Function
Err_Handler:
MsgBoxFa "err"
End Function
اینم sp اماده:
CREATE PROCEDURE dbo.sp_Hijri_Date
AS
DECLARE @a datetime
DECLARE @Y int,@M INT,@D INT,@YY int,@MM INT,@DD INT,@T varchar(6)
set @a=getdate()
set @Y =(Year(getdate()))
set @M = (Month(getdate()))
set @D = (Day(getdate()))
If (@M = 1 And @D < 21 )
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D + 10
End
If @M = 1 And @D > 20
BEGIN
set @YY = @Y - 622
set @MM = @M + 10
set @DD = @D - 20
End
If @M = 2 And @D < 20
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D+ 11
End
If @M = 2 And @D > 19
BEGIN
set @YY = @Y - 622
set @MM = @M + 10
set @DD = @D - 19
End
If @M = 3 And @D < 21
BEGIN
set @YY = @Y - 622
set @MM = @M + 9
set @DD = @D+ 9
End
If @M = 3 And @D > 20
BEGIN
set @YY = @Y- 621
set @MM = @M - 2
set @DD = @D- 20
End
If @M = 4 And @D < 21
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D+ 11
End
If @M = 4 And @D > 20
BEGIN
set @YY = @Y - 621
set @MM = @M- 2
set @DD = @D - 20
End
If @M = 5 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 10
End
If @M = 5 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If @M = 6 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 10
End
If @M = 6 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If @M = 7 And @D < 23
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 7 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 22
End
If @M = 8 And @D < 23
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 8 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 22
End
If @M = 9 And @D < 23
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 9 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 22
End
If @M = 10 And @D < 23
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D + 8
End
If @M = 10 And @D > 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 22
End
If @M = 11 And @D < 22
BEGIN
set @YY = @Y - 621
set @MM = @M - 3
set @DD = @D+ 9
End
If @M = 11 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D- 21
End
If @M = 12 And @D < 22
BEGIN
set @YY = @Y- 621
set @MM = @M - 3
set @DD = @D + 9
End
If @M = 12 And @D > 21
BEGIN
set @YY = @Y - 621
set @MM = @M - 2
set @DD = @D - 21
End
If (Right(@Y, 2) % 4 = 0 And @M > 2)
BEGIN
set @DD = @DD+ 1
If @MM <= 6
BEGIN
If @DD > 31
BEGIN
set @DD = 1
set @MM= @MM + 1
End
else if @MM > 6
BEGIN
If @DD > 30
BEGIN
set @DD = 1
set @MM= @MM + 1
End
End
If @MM = 12 And @DD= 30
BEGIN
set @MM=1
set @dd=1
set @yy=@yy+1
End
end
End
If (Right(@Y, 2) - 1)%4 = 0 And @M <= 3
BEGIN
If Not ( @M = 3 And @D > 20)
BEGIN
set @DD= @DD + 1
If @DD = 31
BEGIN
set @DD = 1
set @MM = @MM + 1
End
End
End
SET @T = Right(str(@YY), 2) + Right('00'+(LTRIM(STR(@MM))), 2) + Right('00'+(LTRIM(STR(@DD))), 2)
select @t as 'Date'
GO
اینم فانکشن تاریخ برا کد نویستون تا بتونین از طریق اون تاریخ سرور رو در کد نویسی داشته باشین
Function Hijri_ShortDate() As String
On Error GoTo Err_Handler
Dim rst As ADODB.Recordset
Dim strsql As String
strsql = "EXECUTE sp_Hijri_Date"
Set rst = New ADODB.Recordset
rst.open strsql, Application.CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If Not rst.EOF Then
Hijri_ShortDate = rst.Fields(0)
End If
rst.Close
Exit Function
Err_Handler:
MsgBoxFa Err.Description, , "dateErr"
End Function
Function Hijri_LongDate()
On Error GoTo Err_Handler
Dim Today As Date, strWeekDay As String, strMonth As String
Today = Now
Select Case Weekday(Today)
Case 1
strWeekDay = "í˜ÔäÈå"
Case 2
strWeekDay = "ÏæÔäÈå"
Case 3
strWeekDay = "Óå ÔäÈå"
Case 4
strWeekDay = "چåÇÑÔäÈå"
Case 5
strWeekDay = "پäÌÔäÈå"
Case 6
strWeekDay = "ÌãÚå"
Case 7
strWeekDay = "ÔäÈå"
End Select
Select Case Mid(Hijri_ShortDate, 3, 2)
Case 1
strMonth = "ÝÑæÑÏíä"
Case 2
strMonth = "ÇÑÏíÈåÔÊ"
Case 3
strMonth = "ÎÑÏÇÏ"
Case 4
strMonth = "撄"
Case 5
strMonth = "ãÑÏÇÏ"
Case 6
strMonth = "ÔåÑíæÑ"
Case 7
strMonth = "ãåÑ"
Case 8
strMonth = "ÂÈÇä"
Case 9
strMonth = "ÂÐÑ"
Case 10
strMonth = "Ïí"
Case 11
strMonth = "Èåãä"
Case 12
strMonth = "ÇÓÝäÏ"
End Select
Dim yy As Integer
yy = Left(Hijri_ShortDate, 2)
Hijri_LongDate = strWeekDay & ", " & Right(Hijri_ShortDate, 2) & " " & strMonth & "," & yy
Exit Function
Err_Handler:
MsgBoxFa "err"
End Function