Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Vo2Ado Empty dates

111 views
Skip to first unread message

Carlos Vazquez

unread,
May 19, 2021, 8:40:10 AM5/19/21
to
Hi!
I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".

How can I pass empty date values to SQLServer?

Thanks!!
****************

aParametros := ArrayCreate(2)

oCmd:CommandText := "INSERT INTO [Ark].[dbo].[Cliente] ( [Ark].[dbo].[Cliente].[Codigo], [Ark].[dbo].[Cliente].[Fecha] ) VALUES (?,?)"

sCliente:GoTop()
WHILE !sCliente:EoF
aParametros[ 1] := sCliente:FIELDGET( #CODIGO )
aParametros[ 2] := sCliente:FIELDGET( #FECHA )
//
oCmd:Execute(@uResult,aParametros,NIL)
IF uResult != 1
MostrarError( oCmd:Error:description )
BREAK
ENDIF
sCliente:Skip()
ENDDO

Phil McGuinness

unread,
May 21, 2021, 10:16:00 AM5/21/21
to
On Wednesday, May 19, 2021 at 10:40:10 PM UTC+10, Carlos Vazquez wrote:
> Hi!
> I'm converting some DBF table to SQLServer. When there is an empty date value, the following code throws an error "type conflict: text is not compatible with date".
>
> How can I pass empty date values to SQLServer?

FUNCTION dtosSQL( dDate AS USUAL ) AS STRING

LOCAL cDate AS STRING

IF IsString(dDate) .OR. dDate = NULL_DATE
cDate := [null]
ELSE
cDate := ['] + Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) + ['] // 20071201 return "2007-12-01"
ENDIF
//
RETURN cDate

Carlos Vazquez

unread,
May 22, 2021, 7:37:16 AM5/22/21
to
El viernes, 21 de mayo de 2021 a las 16:16:00 UTC+2, Phil McGuinness escribió:
> FUNCTION dtosSQL( dDate AS USUAL ) AS STRING
>
> LOCAL cDate AS STRING
>
> IF IsString(dDate) .OR. dDate = NULL_DATE
> cDate := [null]
> ELSE
> cDate := ['] + Stuff(Stuff(DToS(dDate),5,0,[-]),8,0,[-]) + ['] // 20071201 return "2007-12-01"
> ENDIF
> //
> RETURN cDate

Phil, that function returns a string. I've tried to use that concept but then i get "error converting string to date". I've tried also to return NIL, NULL, NULL_DATE... to no avail.
The only thing that works is returning a valid date, say CToD( '01/01/1900' )

Jamal

unread,
May 22, 2021, 12:42:13 PM5/22/21
to
In your app, try calling

AdoConvertNullStringToNull ( TRUE )


P.S. I don't use the Vo2Ado, but I looked at the documentation. Source:


FUNCTION AdoConvertNullStringToNull ( lSet ) AS

Argument(s)

lSet When TRUE, NULL_STRINGS will be converted to NULL, else an Empty
value is passed to COM. The default behaviour is NOT to convert them to NULL


Return


The process of converting NULL_STRINGS can be switched ON/OFF by calling
the AdoCheckNullString function


See Also

Support functions, AdoCheckNullString, Data Types


HTH,
Jamal

Carlos Vazquez

unread,
Oct 31, 2023, 2:42:43 PM10/31/23
to
Jamal, sorry for ultra-late response... :)
I was (again) looking for an answer to "How to send NULL values to SQL servers" and see this message. Your anwser is the one I needed. Thx!!!
0 new messages