The said:
Hi,
Does anyone know the DateSerial replacement function for SQL2K? I have the
following formula in Access that now needs to be added to a store procedure
in SQL.
DateSerial(Year(Date()),Month(Date())+1,0)
Any suggestions would help.
Here is a rudimentary start of a VBA function equivalent that also helps
explain some of the limitations of the DateSerial function:
Public Function MyDateSerial(intYear As Integer, intMonth As Integer,
intDay As Integer) As Variant
Const YearMin = 0
Const YearMax = 9999
Dim lngYear As Long
Dim lngMonth As Long
Dim strTemp As String
MyDateSerial = "Error"
lngYear = intYear
If lngYear < YearMin Or lngYear > YearMax Then Exit Function
If lngYear < 100 Then lngYear = lngYear + 1900
lngMonth = intMonth
lngYear = lngYear + Sgn(lngMonth) * (Abs(lngMonth) - 1) \ 12
lngMonth = lngMonth - 12 * Sgn(lngMonth) * ((Abs(lngMonth) - 1) \ 12)
'Handle any negative months remaining
If lngMonth <= 0 Then
Select Case lngMonth
Case -12:
lngMonth = 12
lngYear = lngYear - 2
Case Else
lngMonth = lngMonth + 12
lngYear = lngYear - 1
End Select
End If
If lngYear - intDay \ 360 <= 100 Or lngYear + intDay \ 360 >= 9999 Then
Exit Function
strTemp = CStr(lngYear) & "-" & CStr(lngMonth) & "-1"
MyDateSerial = DateAdd("d", intDay - 1, CDate(strTemp))
End Function
Maybe that will help somehow in creating a T-SQL function or a .NET
assembly (compiled function or subroutine) that can be called by T-SQL.
See:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/7659aa8da865bac7
and
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/c3baeb7fea00a21
for information about using .NET functions in T-SQL.
Another alternative is to note that you are simply trying to get the
date of the first of the next month (along the lines of the following
'aircode' Access expression):
IIf(Month(Date()) = 12, CDate(Year(Date()) + 1 & "-" & "01" & "-" &
"01"), CDate(Year(Date()) & "-" & Month(Date()) + 1 & "-" & "01"))
James A. Fortune
(e-mail address removed)
