Converting MS Access 2013 Functions to SQL Server functions
I'm fairly new to SQL Server and using the Developer 14 Version
I need to move some functionality from Access 2013 to SQL Server. As part of a conversion, in Access I have a function 'convertDateCCCCMMYY' that tries to convert date information (CCCC, MM and DD) coming in from legacy data into an actual date. The data coming in has a lot of garbage that has to be checked for. If a valid date cannot be created from the garbage passed, the default is to return the date 12/31/2099, which is what is in the constant 'cBadDate'.
I need to convert 'convertDateCCCCMMYY' to a SQL function.
The function 'convertDateCCCCMMYY' also calls functions 'IsLeapYear' and 'zeroPadPrefix' which are included below and need converted to SQL Functions as well.
Can anyone show how these would look converted to SQL functions?
I need to move some functionality from Access 2013 to SQL Server. As part of a conversion, in Access I have a function 'convertDateCCCCMMYY' that tries to convert date information (CCCC, MM and DD) coming in from legacy data into an actual date. The data coming in has a lot of garbage that has to be checked for. If a valid date cannot be created from the garbage passed, the default is to return the date 12/31/2099, which is what is in the constant 'cBadDate'.
I need to convert 'convertDateCCCCMMYY' to a SQL function.
The function 'convertDateCCCCMMYY' also calls functions 'IsLeapYear' and 'zeroPadPrefix' which are included below and need converted to SQL Functions as well.
Can anyone show how these would look converted to SQL functions?
Public Function convertDateCCCCMMYY(passedCCCC As Long, _
passedMM As Long, _
passedDD As Long, _
Optional passedHR As Long = 0, _
Optional passedMin As Long = 0) As Date
'
Dim wkCCCCStr As String
Dim wkMMStr As String
Dim wkDDStr As String
Dim wkHRStr As String
Dim wkMinStr As String
'
Dim wkCCCCNum As Long
Dim wkMMNum As Long
Dim wkDDNum As Long
Dim wkHRNum As Long
Dim wkMinNum As Long
'
wkCCCCNum = passedCCCC
wkMMNum = passedMM
wkDDNum = passedDD
wkHRNum = passedHR
wkMinNum = passedMin
'
' wouldn't be in this routine unless year was greater than 0000
' still, some months coming as zero with an accurate year and day, default to 1
'
' Years were cuainf bad write on SQL Server (but not Access) so these have to be edited
'
' Bizare years
'
If wkCCCCNum < 1900 Then
convertDateCCCCMMYY = cBadDate
Exit Function
End If
'
' Bizare months
'
If wkMMNum < 1 Or wkMMNum > 12 Then
convertDateCCCCMMYY = cBadDate
Exit Function
End If
'
' Bizare Days
'
If wkDDNum < 1 Or wkDDNum > 31 Then
convertDateCCCCMMYY = cBadDate
Exit Function
End If
'
If wkMMNum = 0 Then
wkMMNum = 1
End If
'
If wkHRNum > 24 Then
wkHRNum = 0
End If
'
If wkMinNum > 60 Then
wkMinNum = 0
End If
'
' some very weird date coming in, 00 in the days, 31 days in a month that only has 30 etc..
'
If wkDDNum = 0 Then
wkDDNum = 1
End If
'
If wkDDNum > 31 Then
wkDDNum = 31
End If
'
' Make sure only 30 days in a 30 day month/ September, April, June, November
'
If wkMMNum = 9 Or wkMMNum = 4 Or wkMMNum = 6 Or wkMMNum = 11 Then
If wkDDNum > 30 Then
wkDDNum = 30
End If
ElseIf wkMMNum = 2 Then
' check leap year
If IsLeapYear(wkCCCCNum) Then
If wkDDNum > 29 Then
wkDDNum = 29
End If
Else
If wkDDNum > 28 Then
wkDDNum = 28
End If
End If
End If
'
wkCCCCStr = zeroPadPrefix(Trim(Str(wkCCCCNum)), 4)
wkMMStr = zeroPadPrefix(Trim(Str(wkMMNum)), 2)
wkDDStr = zeroPadPrefix(Trim(Str(wkDDNum)), 2)
wkHRStr = zeroPadPrefix(Trim(Str(wkHRNum)), 2)
wkMinStr = zeroPadPrefix(Trim(Str(wkMinNum)), 2)
'
' If Hr and Min were passed add them to date sent back, otherwise just send date
'
If wkHRNum > 0 Or wkMinNum > 0 Then
convertDateCCCCMMYY = CDate(wkMMStr & "/" & wkDDStr & "/" & wkCCCCStr & " " & wkHRStr & ":" & wkMinStr)
Else
convertDateCCCCMMYY = CDate(wkMMStr & "/" & wkDDStr & "/" & wkCCCCStr)
End If
'
End Function
Public Function IsLeapYear(Optional ByVal intYear As Long) As Boolean
If intYear = 0 Then
intYear = Year(Date)
End If
IsLeapYear = Day(DateSerial(intYear, 2, 29)) = 29
End Function
Public Function zeroPadPrefix(passedNum As Long, passedLength As Long) As String
'
Dim wkStringNum As String
Dim wkNumLength As Long
wkStringNum = Trim(Str(passedNum))
'
' now must zero fill to the left
'
wkNumLength = Len(wkStringNum)
'
Do While wkNumLength < passedLength
wkStringNum = "0" & wkStringNum
wkNumLength = Len(wkStringNum)
Loop
'
zeroPadPrefix = wkStringNum
'
End Function
What prompted you to write this function? There are lots of date functions that can be used to solve this problem.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What prompted you to write this function? There are lots of date functions that can be used to solve this problem.A couple things:
1. Rather than reject every date that wasn't perfect, I was trying to salvage as many dates as I could from the old data. The logic in this function evolved over times as more and more anomolies were found in the date fields. I realize that could be good and bad in the event that the derived date was not correct.
2. Back when I fist started using Access I had some experiences where the built in functions didn't behave as I wanted them to. A good example was IsNumeric. I wanted to know if the entire string was numbers but it came back as true even if there were some non-number characters in the string. That's when I wrote my own function IsANumber(). I shied away from the built in validation functions since then. I Know there were other examples but it was so long ago I don't remember any specifics.
ASKER
Thanks to all. I chose Mark's solution as the best because my simple, beginner SQL mind understands it.
@Scott, would be inclined to use TRY_CAST in that scenario.... Nice...
@Mark:
I thought ISDATE was easier than TRY_CAST, although I understand that my code is not as simplistic. It's set-oriented style, like SQL, not line-by-line code, like VB, etc..
I thought ISDATE was easier than TRY_CAST, although I understand that my code is not as simplistic. It's set-oriented style, like SQL, not line-by-line code, like VB, etc..
ASKER
Scott, would it be possible for you to explain your code a bit? I am quite the SQL novice but have already seen the power of SQL in Set based processing and really want to become better at it and use more techniques that take advantage of SQL.
I know it's a lot to ask and I completely understand if you don't have the time or inclination to provide any detail.
Either way, thanks for the many excellent answers and suggestions you've given me over the years.
I know it's a lot to ask and I completely understand if you don't have the time or inclination to provide any detail.
Either way, thanks for the many excellent answers and suggestions you've given me over the years.
No problem. Besides, I shouldn't have used the CROSS APPLY in this case and that made it more complex than it needed to be. Here's the refined code:
SELECT
CASE WHEN ISDATE(char_date) = 0 THEN bad_date
ELSE DATEADD(MINUTE, passedMin, DATEADD(HOUR, passedHR,
CAST(char_date AS datetime))) END AS ResultDate
FROM (
SELECT
CAST(@passedCCCC * 10000 + @passedMM * 100 + @passedDD AS char(8)) AS char_date, --convert from ints to char YYYYMMDD
CASE WHEN @passedHR IS NULL OR @passedHR < 1 OR @passedHR > 23 THEN 0 ELSE @passedHR END AS passedHR,
CASE WHEN @passedMin IS NULL OR @passedMin < 1 OR @passedMin > 59 THEN 0 ELSE @passedMin END AS passedMin,
CAST('20991231' AS datetime) AS bad_date
) AS set_control_values
A "FROM" is the standard way that a query gets data, so I used that approach, rather than SETs, to edit and adjust the incoming parameter values. Also, DECLAREing variables within functions is a fair amount of overhead, so it should be avoided whenever possible. Finally, in-line-table-valued-funct
OK, back to details on this specific function. The SELECT assigns new column names to the edited/adjusted values, again consistent with typical SQL. In effect these act like variables here, but as a built-in part of a query, not requiring a separate variable address space or separate statements.
The first column in the SELECT takes the incoming cccc, mm and dd and turns them into a single number in the format CCCCMMDD, so that it can be converted to the string 'YYYYMMDD'. All this is done because SQL uses strings to represent date literals, not ints. This value is then given a meaningful name, of "char_date", to accurately reflect what it contains.
The next two columns simply check whether the passed values for HR and Min are valid. If so, the value passed is used, else it's set to 0. You can read "CASE WHEN" as "IF", so "CASE WHEN A = B" in SQL is like "IF A = B" in a more conventional language.
The last column assigns a name to the "bad" value to be returned. This is done for 2 reasons: (1) so that the bad return value is set in only one place, and thus would only have to changed in one place; (2) so that it's clear in the code when a bad value is being returned, rather than just being some "magic" value that appears there.
Note that in the other function, the bad return value is set in at least 6 different places. Now, yes, this particular function is basic enough that you readily identity the 6 places you have to make the change, but not all functions are this basic. That multiple-assignment type of thing is a pain when code needs changed later. And in some procs, the "bad" return value might be a valid value in some other context, and it's very difficult to tell later whether all such values should be changed or not, if the bad value is just a literal value like other literal values.
SELECT
CASE WHEN ISDATE(char_date) = 0 THEN bad_date
ELSE DATEADD(MINUTE, passedMin, DATEADD(HOUR, passedHR,
CAST(char_date AS datetime))) END AS ResultDate
FROM (
SELECT
CAST(@passedCCCC * 10000 + @passedMM * 100 + @passedDD AS char(8)) AS char_date, --convert from ints to char YYYYMMDD
CASE WHEN @passedHR IS NULL OR @passedHR < 1 OR @passedHR > 23 THEN 0 ELSE @passedHR END AS passedHR,
CASE WHEN @passedMin IS NULL OR @passedMin < 1 OR @passedMin > 59 THEN 0 ELSE @passedMin END AS passedMin,
CAST('20991231' AS datetime) AS bad_date
) AS set_control_values
A "FROM" is the standard way that a query gets data, so I used that approach, rather than SETs, to edit and adjust the incoming parameter values. Also, DECLAREing variables within functions is a fair amount of overhead, so it should be avoided whenever possible. Finally, in-line-table-valued-funct
OK, back to details on this specific function. The SELECT assigns new column names to the edited/adjusted values, again consistent with typical SQL. In effect these act like variables here, but as a built-in part of a query, not requiring a separate variable address space or separate statements.
The first column in the SELECT takes the incoming cccc, mm and dd and turns them into a single number in the format CCCCMMDD, so that it can be converted to the string 'YYYYMMDD'. All this is done because SQL uses strings to represent date literals, not ints. This value is then given a meaningful name, of "char_date", to accurately reflect what it contains.
The next two columns simply check whether the passed values for HR and Min are valid. If so, the value passed is used, else it's set to 0. You can read "CASE WHEN" as "IF", so "CASE WHEN A = B" in SQL is like "IF A = B" in a more conventional language.
The last column assigns a name to the "bad" value to be returned. This is done for 2 reasons: (1) so that the bad return value is set in only one place, and thus would only have to changed in one place; (2) so that it's clear in the code when a bad value is being returned, rather than just being some "magic" value that appears there.
Note that in the other function, the bad return value is set in at least 6 different places. Now, yes, this particular function is basic enough that you readily identity the 6 places you have to make the change, but not all functions are this basic. That multiple-assignment type of thing is a pain when code needs changed later. And in some procs, the "bad" return value might be a valid value in some other context, and it's very difficult to tell later whether all such values should be changed or not, if the bad value is just a literal value like other literal values.
ASKER
Thank you for taking the time to explain Scott. So much to learn.
the function already exists....