Prev: Using Parameters and Loop to find all SQL Server versions
Next: import file problem
From: colbymack on 12 Aug 2010 11:14
I have a custom program that inserts data to a table in SQL Server 2000. One
of the fields is a date field, and we are passing it 8 spaces in the flat
file. The program in SQL Server 2000 converts this to null, and does so
using the following code:
begin-procedure Default_Date(:$DateVar, $DefaultDate)
if isblank($DateVar)
let $DateVar = $DefaultDate
else
extract $MM from $DateVar 0 2
extract $DD from $DateVar 2 2
extract $YYYY from $DateVar 4 4
let $myDate = $YYYY || $MM || $DD
do Format-DateTime($myDate, $DateVar, {DEFCMP}, '', 'native')
!end-if
end-procedure

That same code in 2008 is resulting in the value being defined as 01/01/1900.
How can I get this to go back to being null in 2008?

From: Jeroen Mostert on 13 Aug 2010 02:37
On 2010-08-12 17:14, colbymack wrote:
> I have a custom program that inserts data to a table in SQL Server 2000. One
> of the fields is a date field, and we are passing it 8 spaces in the flat
> file. The program in SQL Server 2000 converts this to null, and does so
> using the following code:
> begin-procedure Default_Date(:$DateVar, $DefaultDate)
> if isblank($DateVar)
> let $DateVar = $DefaultDate
> else
> extract $MM from $DateVar 0 2
> extract $DD from $DateVar 2 2
> extract $YYYY from $DateVar 4 4
> let $myDate = $YYYY || $MM || $DD
> do Format-DateTime($myDate, $DateVar, {DEFCMP}, '', 'native')
> !end-if
> end-procedure
>
> That same code in 2008 is resulting in the value being defined as 01/01/1900.
> How can I get this to go back to being null in 2008?
>
I gather this is SQR. Try contacting Oracle for support on SQR, which is not
part of an SQL Server installation. I could hazard a few guesses, but to
understand what's going on you need to understand the conversion and/or the
insertion process, and I don't know SQR.

If it helps, for SQL Server 2008 1/1/1900 is the value of CONVERT(DATETIME,
'') and CONVERT(DATETIME, ' '), i.e. converting a blank string to a
datetime yields the first possible date. I no longer have an SQL Server 2000
instance on hand to see if it exhibits the same behavior, but I'd be
surprised if it didn't. It may be subject to connection options, but I'm not
aware of that.

If this is indeed your problem, you'll need to change your program so it
passes an actual NULL value and doesn't rely on string conversion. Since I
don't know SQR, I don't know how to do that. However, you may want to check
if Default_Date is called passing null for $DefaultDate -- if not, it
probably should. If that's not the issue, it could be an ODBC or OLEDB
problem with the SQR runtime itself.

--
J.
 | 
Pages: 1
Prev: Using Parameters and Loop to find all SQL Server versions
Next: import file problem