Prev: Menu and toolbar
Next: Insert pictures into Exceltable
From: Massimo Petrillo on 12 Feb 2010 08:43
Hi,
I need to run a sp with some datetime parameters.
This is my code snippet:


oCmd :=AdoCommand{}
oPars := oCmd:PARAMETERS_
oCmd:CommandText := "mysq"
oCmd:CommandType := AdCmdStoredProc
oCmd:ActiveConnection:= self:oConnection
oPars :=
oCmd:PARAMETERS_
oPars:Item("@dDate"):VALUE:= self:oDCsleDate:VALUE

This results on some machines as:


declare @P1 int
set @P1=1
exec mysq 'Feb 12 2009 12:00:00:000AM', @P1 output
select @P1


while on other computer I get

declare @p14 int
set @p14=89
exec mysq '2009-02-12 00:00:00:000',@p14 output
select @p14

In the second case SQLServer converts the date to December 2th 2009.
Are there some ways to force the former behaviour on every computer?
Does it depends on sqlserver settings?


Thanks in advance.
Massimo
From: Willie Moore on 12 Feb 2010 09:16
Massimo,

Have you tried passing in the dtos(SELF:oDCsleDate:Value) or
dtoc(SELF:oDCsleDate:Value)?

Regards,
Willie

"Massimo Petrillo" <massimo.petrillo(a)gmail.com> wrote in message
news:87b27803-4c00-4e8f-b5c0-f47255585652(a)j31g2000yqa.googlegroups.com...
> Hi,
> I need to run a sp with some datetime parameters.
> This is my code snippet:
>
>
> oCmd :=AdoCommand{}
> oPars := oCmd:PARAMETERS_
> oCmd:CommandText := "mysq"
> oCmd:CommandType := AdCmdStoredProc
> oCmd:ActiveConnection:= self:oConnection
> oPars :=
> oCmd:PARAMETERS_
> oPars:Item("@dDate"):VALUE:= self:oDCsleDate:VALUE
>
> This results on some machines as:
>
>
> declare @P1 int
> set @P1=1
> exec mysq 'Feb 12 2009 12:00:00:000AM', @P1 output
> select @P1
>
>
> while on other computer I get
>
> declare @p14 int
> set @p14=89
> exec mysq '2009-02-12 00:00:00:000',@p14 output
> select @p14
>
> In the second case SQLServer converts the date to December 2th 2009.
> Are there some ways to force the former behaviour on every computer?
> Does it depends on sqlserver settings?
>
>
> Thanks in advance.
> Massimo
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4860 (20100212) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4860 (20100212) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



From: Massimo Petrillo on 12 Feb 2010 10:16
On 12 Feb, 15:16, "Willie Moore" <will...(a)wmconsulting.com> wrote:

> Have you tried passing in the dtos(SELF:oDCsleDate:Value) or
> dtoc(SELF:oDCsleDate:Value)?
>
I get an error because the parameter is a DATETIME and the conversion
failed.
From: Ginny Caughey on 12 Feb 2010 10:20
Massimo,

What is your DateTimeAsDate setting for VO2Ado? That might make a
difference.

--

Ginny Caughey
www.wasteworks.com




"Massimo Petrillo" <massimo.petrillo(a)gmail.com> wrote in message
news:20bf80b9-c804-478c-ac7b-2b8ba5b2b1ba(a)z19g2000yqk.googlegroups.com...
> On 12 Feb, 15:16, "Willie Moore" <will...(a)wmconsulting.com> wrote:
>
>> Have you tried passing in the dtos(SELF:oDCsleDate:Value) or
>> dtoc(SELF:oDCsleDate:Value)?
>>
> I get an error because the parameter is a DATETIME and the conversion
> failed.

From: Geoff Schaller on 13 Feb 2010 20:13
Massimo.

Probably your datetime settings as Ginny suggested are wrong (which
default are you using - read the VO2Ado help on this) but maybe it is
better in the parameters to send an ADODateTime object.

Also, when creating strings for date conversion by SQL Server you need
to use the universal method: YYYYMMDD HH:MM:SS but if it is midnight on
that day (ie no time) you do not need to specify the time component. Now
you can use DTOS(dDate) in VO to obtain this and it will not be affected
by regional settings.

Geoff



"Massimo Petrillo" <massimo.petrillo(a)gmail.com> wrote in message
news:87b27803-4c00-4e8f-b5c0-f47255585652(a)j31g2000yqa.googlegroups.com:

> Hi,
> I need to run a sp with some datetime parameters.
> This is my code snippet:
>
>
> oCmd :=AdoCommand{}
> oPars := oCmd:PARAMETERS_
> oCmd:CommandText := "mysq"
> oCmd:CommandType := AdCmdStoredProc
> oCmd:ActiveConnection:= self:oConnection
> oPars :=
> oCmd:PARAMETERS_
> oPars:Item("@dDate"):VALUE:= self:oDCsleDate:VALUE
>
> This results on some machines as:
>
>
> declare @P1 int
> set @P1=1
> exec mysq 'Feb 12 2009 12:00:00:000AM', @P1 output
> select @P1
>
>
> while on other computer I get
>
> declare @p14 int
> set @p14=89
> exec mysq '2009-02-12 00:00:00:000',@p14 output
> select @p14
>
> In the second case SQLServer converts the date to December 2th 2009.
> Are there some ways to force the former behaviour on every computer?
> Does it depends on sqlserver settings?
>
>
> Thanks in advance.
> Massimo

 | 
Pages: 1
Prev: Menu and toolbar
Next: Insert pictures into Exceltable