YYYYMMDD string passed to SQL7 Datetime SP parameter
-
Hi,
I have an application that has been running for 5 years perfectly happily. It has occasionally thrown up untrapped errors that have just disappeared when moved to an alternate machine.
After much pain I have tracked this down to the implicit conversion of dates being passed to a stored procedure. The ADO command object builds a string that contains the date string in the format YYYYMMDD and the parameter datatype it is passed to is datetime. Because of what appears to be the inconsistent nature of this error does anyone now of any environmental settings that would affect the formatting of this datestring?
i am unable to amend the actual code to cure the ambiguous dates that are obviously possible using this method of type conversion due to a large number of interfaces based on the dates produced so really do need to track down the cause as a resolution.
Its SQL7, MDAC 2.5 SP1 and VB6.
thanks.
rodger
- SQL Server datetime datatype allows for a date value from 1/1/1753 through 12/31/9999 and a time accuracy to one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). So a time value of '23:59:59.999' is rounded to '00:00:00.000', so the closest you can get to before mid-night is '23:59:59.998' and after mid-night is '00:00:00.002', otherwise '23:59:59.999' thru '00:00:00.001' = ''00:00:00.000' which IS NULL.SQL Server smalldatetime datatype allows for a value from 1/1/1900 through 6/6/2079 and a time accuracy to the minute. So a time with a value of '12:35:29.998' is rounded to '23:35' while '12:35:29.999' is rounded to '23:36'The VB Date datatype is closest to the SQL Server smalldatetime for it's resolution, but differs as it allows for a value from 1/1/100 through 12/31/9999 and a time accuracy to the second.For VB and SQL Server 2000, "12:00:00 AM" IS NULL for the time portion of a datetime or smalldatetime or Date, therefore a valid date is "12:00:01 AM" for midnight (or "00:00:00.002" for 24 hour format).There is a slightly more confusing situation for the Date portion of a datetime, for SQL Server the Date portion of the datetime = '1/1/1900' means that is the "base date" and it IS NULL, For VB the smallest date is 1/1/100 while for SQL Server the smallest date is 1/1/1900.In VB to test a Date datatype for a NULL Date and Time is: If MyDate = #12:00:00 AM# Then "NULL Date and Time" Else "NOT NULL Date and Time" End If.The following from SQL Server SELECT CAST('' AS datetime) returns :1900-01-01 00:00:00.000Which means that both the Date and Time portions are NULL.The VB Date data type is closest a SQL Server smalldatetime, the VB application defaults should be MinDate = #1/1/1900# and MaxDate = #6/6/2079#, which are the limits for the smalldatetime data type in SQL Server. However you can safely increase to MaxDate = #12/31/9999# since VB allows this, but only if you are always using the SQL Server datetime.You non-US users will see that the VB date string format I cite is in MDY order, VB uses the Local machine's Windows Regional settings for this format so the application will need to handle this as well, while the use of the ISO date string format makes this easy in SQL Server.Andy
-
Dunno about VB datetimes but in SQL Server 2000...
I think you'll find that 23:59:59.999 will round up to precisely 00:00:00.000 and that 23:59:59.998 will round down to 23:59:59.997.
SELECT CAST('23:59:59.999' AS DATETIME)
SELECT CAST('23:59:59.998' AS DATETIME)
SELECT CAST('23:59:59.997' AS DATETIME)
...so the closest you can get to before mid-night is '23:59:59.998' and after mid-night is '00:00:00.002', otherwise '23:59:59.999' thru '00:00:00.001' = ''00:00:00.000' which IS NULL.
Absolutely not true... I think you'll find that the time portion of a datetime is never NULL if a date is present and that a time of 00:00:00.000 IS possible and frequently used.
SELECT CAST('00:00:00.000' AS DATETIME)
SELECT CAST('20060630' AS DATETIME)
There is a slightly more confusing situation for the Date portion of a datetime, for SQL Server the Date portion of the datetime = '1/1/1900' means that is the "base date" and it IS NULL...The following from SQL Server SELECT CAST('' AS datetime) returns :1900-01-01 00:00:00.000Which means that both the Date and Time portions are NULL.A zero value is not the same as a NULL value... An empty string ('') is not the same as a NULL... NULL is not "Nothing"... NULL is NULL.
SELECT CAST(0 AS DATETIME)
SELECT CAST(NULL AS DATETIME)
SELECT CAST('' AS DATETIME)
...therefore a valid date is "12:00:01 AM" for midnight (or "00:00:00.002" for 24 hour format).That's absolutely not true... 12:00:01 AM is no more midnight than NULL is nothing...
SELECT CAST('00:00:00' AS DATETIME)
SELECT CAST('20060630' AS DATETIME)
SELECT CAST('00:00:00' AS SMALLDATETIME)
SELECT CAST('20060630' AS SMALLDATETIME)
I'm not sure what your source of information about dates, times, and NULLs is but you should check out what Books OnLine has to say.
By the way, there's an underlying "date serial" number for each date/time and it's based on the FLOAT datatype which is why certain times like 23:59:59:999 get rounded... FLOAT is one of those nasty "approximate" data types based on binary math. The numbers to the left of the decimal point represent the number of whole days since midnight 19000101 and the numbers to the right represent the number of fractional days which is interpreted as time... that's why the following produces a datetime of 2006-06-30 08:49:12.043 and 0 produces a date time of 1900-01-01 00:00:00.000... that's also why you cannot have a NULL time if a date is present... you cannot have a value of 38896.NULL, for example...
SELECT CAST(38896.367500501547 AS DATETIME)
SELECT CAST(0 AS DATETIME)
SELECT CAST(38896 AS DATETIME)
GO
SELECT CAST(38896.NULL AS DATETIME)
I do agree that ISO dates should always be used except for reporting purposes. More than that, if you have a world-wide presence, you should use UTC dates with offsets to produce local times.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Gents,
Many thanks for taking the time to respond but I cant have clearly defined my issue.
I have an ADO Connection object. The SQL statement is built as a string and includes all the input parameters for the SP that it is to execute. I am fully aware that a command object would have done this job better but need to understand why all of a sudden the string to datetime conversion of the input parameter has stopped working in my environment but continues to work in the Production environment. This code wasnt mine in the first place so its looking like a rewrite at present.
thanks.
Rodger
-
Rodger,
Did anyone change the default datatime format on the non-production box?
This is why you should not trust defaults... things change.
Perhaps, if you posted the code, we may be able to better help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As usual no one is admitting to changing anything.
With p_oCom
For l_lCounter = l_lLBound To l_lUBound
l_cData = p_vData(l_lCounter)
If Len(l_cData) = 0 Then
l_cData = ""
End If
Debug.Print .Parameters(l_lCounter).Type
Select Case p_aeValidation(l_lCounter).eType 'tValidation.eType
Case edtEntity.edtDate
If l_cData = "NULL" Then
l_cData = "19000101"
End If
If .Parameters(l_lCounter).Type = adDBTimeStamp Then
.Parameters(l_lCounter).Type = adDate
End If
.Parameters(l_lCounter).Value = DateSerial(CInt(Right(l_cData, 4)), CInt(Mid(l_cData, 3, 2)), CInt(Left(l_cData, 2)))
____________________________________________________
All the dates parameters are declared as datetime and all I get back from ADO is an Insertion Error.
Thanks.
Rodger
-
Like you said, the code has been running fine except for the occasional hiccup... the problem must be in the data somewhere you are not trapping for an error.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
I have now resolved this by changing the call to the SP to use the Command object rather than Connection object. I narrowed the issue down to the conversion of the parameters in the string used in the Execute on the connection object. ie convert a date to a string then the Execute has to convert it back to a date. Command object means I pass a date to a date parameter and no conversion is required.
I inherited this horribly written bit of code and had to cobble a resolution together, thanks to all that took the time to respond.
Rodger
-
Thank you for the feed back on your resolution! It's a keeper...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply