Did this here for this client and they liked it enough to put it into
their framework base class; thought I'd share it with the community for
anyone who struggles with those darn 1900-1-1 dates in SQL Server:
Here’s code to cycle through the [SQL Server] datetime fields and reset
them to Fox’s blank date value should the value be 1900/1/1.
******************************************************************
* FUNCTION NAME: ResetDates
*
* AUTHOR, DATE:
* Michael J. Babcock, 8/7/2006
*
* PROCEDURE DESCRIPTION:
* Cycles through cursor and resets dates to {/} where they're
DATE(1900,1,1) or NULL (useful for SQL Server table fields)
******************************************************************
LPARAMETERS tcCursor
LOCAL laFields(1), liNumFields as Integer, lcCursor as String, liLoop as
Integer, lcCmd as String
IF VARTYPE(tcCursor) = "C" AND NOT EMPTY(tcCursor) THEN && use passed parm
lcCursor = tcCursor
ELSE && use default cursor
lcCursor = this.cCursor
ENDIF
IF NOT EMPTY(lcCursor) AND USED(lcCursor) THEN
liNumFields = AFIELDS(laFields,lcCursor) && get fields into local array
lcCmd = "REPLACE ALL "
FOR liLoop = 1 TO liNumFields && build command for all datetime fields
IF INLIST(laFields(liLoop,2),"D","T") AND UPPER(laFields(liLoop,1)) <>
"DLAST_MODIFIED" THEN
lcCmd = lcCmd + laFields(liLoop,1) + " with IIF(" + laFields(liLoop,1) +
"DATE(1900,1,1),{}," + laFields(liLoop,1) + "), "
ENDIF
ENDFOR
* rip off rightmost comma
lcCmd = ALLTRIM(lcCmd)
IF RIGHT(lcCmd,1) = [,] THEN && there's something to modify
lcCmd = SUBSTR(lcCmd,1,LEN(lcCmd)-1) + [ in ] + lcCursor
&lcCmd && execute built command
TABLEUPDATE(.t.,.t.,lcCursor) && reset modification flags
GO TOP IN (lcCursor)
ELSE
&& do nothing
ENDIF
ENDIF && NOT EMPTY(lcCursor) AND USED(lcCursor)
--
Thanks,
--Michael
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
