Dates rendering incorrectly

Hi,I have a strange problem occuring on a scatter graph. The graphs x axis are susposed to be dates. But if there is no information on that day then the label displays an internal date format (number of days since 1/1/1970 I believe)I have a screenshot here:http://members.iinet.net.au/~y0da/dotnet/mechprop.GIFDoes anyone have any suggestions how to stop this occuring?

you could use an ISNULL function in your sql. something like :
SELECT
...
ISNULL(datecolumn,'')
FROM
...
and if you are using Date functions you could substitute 0 for null or empty values.

|||The sql is not outputing the suspect dates, its reporting services. There are no records coming from the db for those dates
|||I prbly didnt understand what you are trying to do but Reporting Services by itself does not produce anything. It only displays the data in the format you specify. If you post the query you have and how you are handling the number of days I can try to help you better.

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)
>The parameter used in the stored procedure comes from a VB6 app, I
>amended this so the format was "yyyymmdd hh:mm:ss".

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:
> 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"))

That's indeed a safe format for datetime values. Nevertheless, you
should use adDBTimeStamp instead, so that binary values are passed
over the wire.

> 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

If data.date_of_call is datetime, there is no need to use convert at
all. Just take it away.

> 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.

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/techin.../2000/books.asp|||On Tue, 26 Apr 2005 21:50:00 +0000 (UTC), Erland Sommarskog wrote:

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

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:

>> Parameters.Append .CreateParameter("date_of_call", adChar, , 17,
>> Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))
> That's indeed a safe format for datetime values. Nevertheless, you
> should use adDBTimeStamp instead, so that binary values are passed
> over the wire.

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:
> 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.

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/techin.../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:
> Just curious, why would you use adDBTimeStamp and not adDate for this?

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/techin.../2000/books.asp|||Joe Celko (jcelko212@.earthlink.net) writes:
> 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.

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/techin.../2000/books.asp

Dates being saved incorrectly

Hi,
I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.

What is a good way to not save blank date fields as "01/01/1900"?

Thanks

Stephen

Two choises:

1). If user doesn't leave the field blank, you can set DateTime = System.DateTime.Now, so that you can get the current dattime;

2). Allow your datetime column in your table as NULL value, or GetDate() as Default value, so that if user didn't put value, you can set the field as NULL or current datetime

Hope it helps

DateDiff calculating ages incorrectly.

Hi,
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
Return @.Age
End
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = Year(@.CurDT) - Year(@.DOB) -
> Case When Month(@.CurDT) < Month(@.DOB) Then 1
> When Month(@.CurDT) > Month(@.DOB) Then 0
> When Day(@.CurDT) < Day(@.DOB) Then 1
> Else 0 End
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>