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?

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

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
For you, it would be a case of checking numbers, then doing the conversion
create function convertDateCCCCMMYY (@passedCCCC int, @passedMM  int, @passedDD  int, @passedHR  int = 0, @passedMin int = 0, @passedSEC int = 0)
returns datetime									
AS
BEGIN

   declare @dt datetime = 0

   IF @passedCCCC not between 1900 and 2900
   return 0

   IF @passedmm not between 1 and 12
   return 0

   IF @passeddd not between 1 and day(eomonth(datefromparts(@passedCCCC,@passedmm,1)))
   return 0

   IF isnull(@passedhr,0) not between 0 and 23
   return 0

   IF isnull(@passedmin,0) not between 0 and 59
   return 0

   IF isnull(@passedSEC,0) not between 0 and 59   
   return 0

set @dt = datetimefromparts(@passedCCCC,@passedMM,@passedDD,isnull(@passedHR,0),isnull(@passedMin,0),isnull(@passedSec,0),0)

return @dt 

END
GO

Open in new window

Any error it will return the 0 date of 1900-01-01 00:00:00

and to test  :  select dbo.convertDateCCCCMMYY (2018,12,13,23,12,14)


PS was waiting for a list of functions....
0
 
Mark WillsTopic AdvisorCommented:
Check out datetimefromparts() https://docs.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql

the function already exists....
0
 
PatHartmanCommented:
What prompted you to write this function?  There are lots of date functions that can be used to solve this problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
The function is below.  Note that no param in a SQL function is truly optional, although you can specify "DEFAULT":

SELECT /*<db_name>.*/dbo.convertDateCCCCMMYY(1945,9,2,14,11), --<db_name> is optional, not needed if the function is in the current db
    dbo.convertDateCCCCMMYY(111,9,2,14,11),
    dbo.convertDateCCCCMMYY(1975,NULL,2,14,11),
    dbo.convertDateCCCCMMYY(2018,3,6,DEFAULT,DEFAULT)


--USE [<your_db_name>} --make sure you are in the correct db before running
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.convertDateCCCCMMYY
( @passedCCCC AS int,
  @passedMM AS int,
  @passedDD AS int,
  @passedHR AS int = 0,
  @passedMin AS int = 0
)
RETURNS datetime
AS
BEGIN
RETURN (
    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 (
        --convert from ints to char YYYYMMDD
        SELECT CAST(@passedCCCC * 10000 + @passedMM * 100 + @passedDD AS char(8)) AS char_date,
            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
    ) AS set_control_values
    CROSS APPLY (
        SELECT CAST('20991231' AS datetime) AS bad_date
    ) AS assign_constant_values
)
END /*FUNCTION*/
GO

Open in new window

1
 
mlcktmguyAuthor Commented:
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.
0
 
mlcktmguyAuthor Commented:
Thanks to all.  I chose Mark's solution as the best because my simple, beginner SQL mind understands it.
0
 
Mark WillsTopic AdvisorCommented:
@Scott, would be inclined to use TRY_CAST in that scenario.... Nice...
0
 
Scott PletcherSenior DBACommented:
@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..
0
 
mlcktmguyAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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-functions often perform vastly better than scalar functions.  In cases where you'd later want to convert your scalar function to in-line, you'd have to remove separate statements, variables, etc., because in-line only allows a single SELECT.  So, if you can, might as well write it that way to begin with.

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.
0
 
mlcktmguyAuthor Commented:
Thank you for taking the time to explain Scott.  So much to learn.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.