Dates incorrectly being saved incorrectly. DD and MM swapped round ?

For some reason a stored procedure which I have created is incorrectly
saving the date to the table. It seems the day and month are being
swapped around e.g. a date which should be the 12th April (12/04/2005)
is saving as the 4th December (04/12/2005).

The parameter used in the stored procedure comes from a VB6 app, I
amended this so the format was "yyyymmdd hh:mm:ss". The full line in VB
being,

Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))

When I run my VB app it works fine, the syntax in the stored procedure
is,

CREATE PROCEDURE dbo.spUpdValues

@data_id int,
@date_of_call datetime

as

update data
SET date_of_call = CONVERT(char, @date_of_call, 101)
where data_id=@data_id

Is it because the convert format is using an american date format ? I
can't see why as I can't reproduce this error using my own PC as the
date saves correctly, I can also confirm it's not happening to everybody
who uses the app. If it is happening for specifc users then what could
be the cause. I've checked Regional Settings and all seems fine there.

Any ideas on what could be doing this as I'm struggling to investigate
any further.

To debug I ran the stored procedure direct, manually inputting the
variable - again no problem. Also, the following SQL statment shows no
problem...

declare @date_of_call datetime
set @date_of_call = '20041101 08:30:00'

select CONVERT(char, @date_of_call, 101)
select CONVERT(char, @date_of_call, 106)

------------------------------
11/01/2004

(1 row(s) affected)

------------------------------
01 Nov 2004

(1 row(s) affected)

Any help would be much appreciated.

*** Sent via Developersdex http://www.developersdex.com ***

MSSQL stores dates internally in a binary format - the display format
in any client (including Query Analyzer etc.) is defined by the client,
not the server. See this article:

http://www.karaszi.com/sqlserver/info_datetime.asp

The best way to format dates is usually to do it in the client
application, because it has access to the client's regional settings.

Simon

On Tue, 26 Apr 2005 10:36:00 GMT, Robert Zirpolo wrote:

(snip)[color=blue]
>The parameter used in the stored procedure comes from a VB6 app, I
>amended this so the format was "yyyymmdd hh:mm:ss".[/color]

Hi Robert,

This format is not one of the guaranteed "safe" formats. I must admit that
I have not yet found any positive evidence that this format IS interpreted
wrong, but since it's not guaranteed, it MIGHT be interpreted wrong.

These formats are safe:

* yyyymmdd - for date only (note: no dashes, slashes, dots, or other
interpuction)

* yyyy-mm-ddThh:mm:ss - for data and time (note: dashes are required
between the parts of the date; colons between the parts of the time and an
uppercase T seperates the date from the time part)

* yyyy-mm-ddThh:mm:ss.ttt - same as above, but including milliseconds

Try using one of these formats and see if that solves your problem.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Robert Zirpolo (robert.zirpolo@moorestephens.com) writes:[color=blue]
> For some reason a stored procedure which I have created is incorrectly
> saving the date to the table. It seems the day and month are being
> swapped around e.g. a date which should be the 12th April (12/04/2005)
> is saving as the 4th December (04/12/2005).
>
> The parameter used in the stored procedure comes from a VB6 app, I
> amended this so the format was "yyyymmdd hh:mm:ss". The full line in VB
> being,
>
> Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
> Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))[/color]

That's indeed a safe format for datetime values. Nevertheless, you
should use adDBTimeStamp instead, so that binary values are passed
over the wire.
[color=blue]
> CREATE PROCEDURE dbo.spUpdValues
>
> @data_id int,
> @date_of_call datetime
>
> as
>
> update data
> SET date_of_call = CONVERT(char, @date_of_call, 101)
> where data_id=@data_id[/color]

If data.date_of_call is datetime, there is no need to use convert at
all. Just take it away.
[color=blue]
> Is it because the convert format is using an american date format ? I
> can't see why as I can't reproduce this error using my own PC as the
> date saves correctly, I can also confirm it's not happening to everybody
> who uses the app. If it is happening for specifc users then what could
> be the cause. I've checked Regional Settings and all seems fine there.[/color]

SQL Server does not go by regional settings, nor on the server, and
nor of the client. Instead SQL Server goes by dateformat and language
settings. Different users can have different default languages.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On Tue, 26 Apr 2005 21:50:00 +0000 (UTC), Erland Sommarskog wrote:

(snip)[color=blue][color=green]
>> update data
>> SET date_of_call = CONVERT(char, @date_of_call, 101)
>> where data_id=@data_id[/color]
>
>If data.date_of_call is datetime, there is no need to use convert at
>all. Just take it away.[/color]

Hi Erland,

Ah, I missed that part (I guess I shouldn't stop reading when I think I
see the problem, eh?)

Good catch!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

On Apr 26 2005, 05:50 pm, Erland Sommarskog <esquel@sommarskog.se> wrote in
news:Xns9644F22BB824Yazorman@127.0.0.1:
[color=blue][color=green]
>> Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
>> Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))[/color]
>
> That's indeed a safe format for datetime values. Nevertheless, you
> should use adDBTimeStamp instead, so that binary values are passed
> over the wire.[/color]

Just curious, why would you use adDBTimeStamp and not adDate for this?

--
remove a 9 to reply by email

I have just re-visited this and am going to go with your suggestion of

"You should use adDBTimeStamp instead, so that binary values are passed
over the wire."

I think this could be the cause, it's still bl**dy strange why it is
happening so infrequently.

Hopefully this will eradicate the problem. Thanks to everybody in
regards to your posts.

*** Sent via Developersdex http://www.developersdex.com ***

Robert Zirpolo (robert.zirpolo@moorestephens.com) writes:[color=blue]
> I have just re-visited this and am going to go with your suggestion of
>
> "You should use adDBTimeStamp instead, so that binary values are passed
> over the wire."
>
> I think this could be the cause, it's still bl**dy strange why it is
> happening so infrequently.[/color]

First of all, you should take that convert thing out. That's your main
problem.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

There is only one format allowed for dates in Standard SQL,
"yyyy-mm-dd" and it is based on the ISO-8601 Standard. You should be
using only this and not any local dialect formats. Let the front end
worry about the display.

Dimitri Furman (dfurman@cloud99.net) writes:[color=blue]
> Just curious, why would you use adDBTimeStamp and not adDate for this?[/color]

Because adDBTimeStamp is the same binary representation as in SQL Server.
(Well, not really since adDBTimeStamp permits for nine-digit fractions
and SQL Server only three.)

adDate on the other hand is a floating point number, with 0 meaning
1899-12-30. Since the base date in SQL Server is 1900-01-01, this can
cause some confusion. This may be covered up behind the scenes, but
in any case that would only be extra conversions. Furthermore, I don't
thiak adDate is able to handle milliseconds.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Joe Celko (jcelko212@earthlink.net) writes:[color=blue]
> There is only one format allowed for dates in Standard SQL,
> "yyyy-mm-dd" and it is based on the ISO-8601 Standard. You should be
> using only this and not any local dialect formats. Let the front end
> worry about the display.[/color]

Please Joe, you are in an SQL Server newsgroup now. Do not give outright
incorrect recommendations. Yes, YYYY-MM-DD may be standard SQL, but
this format may be misinterpreted:

set language us_english
select convert(datetime, '2005-04-12') -- Prints 2005-04-12
go
set language German
select convert(datetime, '2005-04-12') -- Prints 2005-12-04

You can use ISO 8601 safely in SQL Server, but then you need to go
all the way (save for the timezone): 2005-04-12T19:12:12. Without the
T, you can get into misery.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp