Quick Tip: Passing Date and Time Variables to SQL Server

As a follow on from yesterday's post, Quick Tip: Passing Variables to SQL Server, I wanted to discuss the related topic about passing of date and time variables.

There are two main issues we see with passing Dates and Times and both can cause SQL Server to throw the following exception:

/* Date: 12/15/2011 Time: 10:44:04
SQLSTATE:(22008) Native Err:(241) stmt(99398048):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.*/

Issue 1 - Date Variables

The first issue relates to the handling of Dates and does not show up in US sites, but does almost everywhere else in the world. It relates to the format of the Date and regional settings. SQL Server expects dates as a string in the format MM/DD/YYYY or YYYYMMDD. So when creating code to pass through to SQL Server I can use the str() function to convert the date to a string.

sqlstring = "select * from GL10000 where POSTDATE = '" & Str(Date) & "'"

This works fine in the United States, but has unexpected results elsewhere. The code would work without errors, but would have incorrect results for the first 12 days of a month, then starting on day 13 we would see the error above being generated. In the really early days of Microsoft Dynamics GP, we (the support team looking after Australia and New Zealand) saw this issue quite a bit with modules that are heavily optimized for SQL (meaning using lots of passthrough SQL from Dexterity).

The problem is that the code was written and tested in the US where the date format specified in the regional settings (MM/DD/YYYY) matches the format that SQL Server expects dates to be provided in. So using the str() function works, but elsewhere in the world the date format specified in the regional settings (DD/MM/YYYY) does not match and so the code fails.

Issue 2 - Time Variables

The second issue is related to Times and again is caused by regional settings. SQL Server expects times as a string in the format HH:MM:SS (24 hour time) or HH:MM:SS XX (12 hour time), where XX is a 2 character case insensitive suffix (AM, am, PM or pm). Now these two formats are the defaults for 24 and 12 hour time and so using the str() function works almost all the time.

sqlstring = "select * from GL10000 where TIME1 = '" & Str(TimeValue) & "'"

It can fail when the suffix characters have been altered by a user (or administrator). For example: the addition of periods/dots/full stops, such as A.M. and P.M. This no longer matches the acceptable formats and the error above would be generated.

Solutions

The solution for the date issue in Dexterity is to use the sqlDate() global function, this breaks down the date and builds a string that will always be in YYYYMMDD format (note the Dexterity function does not add the single quotes like SQL_FormatStrings so these need to be added in the code).

The solution for the time issue at this stage is to change the regional settings to 24 hour time (where the suffix is not used) or to change the suffixes back to the supported formats of AM/PM or am/pm. As this issue is very rare and easily fixed, a Dexterity sqlTime() function has not been created, but you can create one if needed.

The Knowledge Base (KB) articles I wrote a while back talk about these issues as well as the single quote issue in strings:

• How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129)
   
• How to pass dates and times to SQL Server from Dexterity in Microsoft Dynamics GP (KB 929786)

So to help VBA developers, I have created a similar sqlDate() function. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.

VBA Code for sqlDate()

Option Explicit

Public Function sqlDate(IN_Date As Date) As String
sqlDate = Right("0000" + Trim(Str(Year(IN_Date))), 4) & _
Right("00" + Trim(Str(Month(IN_Date))), 2) & _
Right("00" + Trim(Str(Day(IN_Date))), 2)
End Function
 

While I am at it, here is a sqlTime() function you can use to get the time in 24 time. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.

VBA Code for sqlTime()

Option Explicit

Public Function sqlTime(IN_Time As Date) As String
sqlTime = Right("00" + Trim(Str(Hour(IN_Time))), 2) & ":" & _
Right("00" + Trim(Str(Minute(IN_Time))), 2) & ":" & _
Right("00" + Trim(Str(Second(IN_Time))), 2)
End Function

Taking it one step further here is a function which handles date and time and also adds the single quotes.

VBA Code for sqlDateTime()

Option Explicit

Public Function sqlDateTime(IN_DateTime As Date) As String
sqlDateTime = "'" & sqlDate(IN_DateTime) & " " & sqlTime(IN_DateTime) & "'"
End Function

Now before I get too many comments telling me this is not the most efficient method, I will mention that VBA has a Format() function that can do most of the formatting for us (including the single quotes).

sqlstring = "select * from GL10000 where POSTDATE = " & Format(Date, "'yyyymmdd'")

sqlstring = "select * from GL10000 where TIME1 = " & Format(TimeValue, "'hh:mm:ss'")

Hope you find this information useful.

David

PS: Yes, I know that the use of parameters as discussed in the comments of the previous post might avoid the need for these functions.

PPS: Let the comments discussion begin...