Incorrect Date Conversion - Help Please

[theintrepidfox@hotmail.com]

Dear Group

Some but not all of the dates are incorrect converted (incremented by 1
day) with the following SQL statement.

SELECT DISTINCT CONVERT(datetim e,cast(Returned ByDate AS int)) AS
ReturnedByDate, CONVERT(varchar (10),ReturnedBy Date,104) AS
LabelReturnedBy Date, ReturnedByDate FROM i2b_keytransact ionhistory
ORDER BY ReturnedByDate ASC

'ReturnedByDate ' is of type smalldatetime in the table. I need the
result set to return me the ReturnByDate once as actual date values
(for the value of a dropdown) and once as text values (for the text of
the same dropdown). It all works fine, it's just that some actual date
values are incremented by one day. The third column in the result set
sample represents the actual database value as a reference to give you
an idea of the original value.

ReturnedByDate LabelReturnedBy Date ReturnedByDate
------------------------- ------------------- -------------------
2004-11-05 00:00:00.000 04.11.2004 2004-11-04 15:22:00
2004-11-11 00:00:00.000 11.11.2004 2004-11-11 00:00:00
2004-11-12 00:00:00.000 12.11.2004 2004-11-12 00:29:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:23:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:24:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 21:10:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 14:50:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:02:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:03:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:09:00
2004-12-09 00:00:00.000 09.12.2004 2004-12-09 08:09:00
Any help is very appreciated!

Thanks for your time & efforts!

Martin

[Kaveman]

Re: Incorrect Date Conversion - Help Please

DECLARE @ReturnedByDate smalldatetime
SET @ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@ReturnedB yDate AS real) AS Y,
cast(@ReturnedB yDate AS int) AS X,
CONVERT(datetim e,cast(@Returne dByDate AS int)) AS
ReturnedByDate, CONVERT(varchar (10),@ReturnedB yDate,104) AS
LabelReturnedBy Date, @ReturnedByDate
-- FROM i2b_keytransact ionhistory ORDER BY ReturnedByDate ASC


select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

[xtremeSQL@gmail.com]

Re: Incorrect Date Conversion - Help Please

DECLARE @ReturnedByDate smalldatetime
SET @ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@ReturnedB yDate AS real) AS Y,
cast(@ReturnedB yDate AS int) AS X,
CONVERT(datetim e,cast(@Returne dByDate AS int)) AS
ReturnedByDate, CONVERT(varchar (10),@ReturnedB yDate,104) AS
LabelReturnedBy Date, @ReturnedByDate
-- FROM i2b_keytransact ionhistory ORDER BY ReturnedByDate ASC


select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

-- Happy Holidays!
-- Kurt

[Erland Sommarskog]

Re: Incorrect Date Conversion - Help Please

(theintrepidfox @hotmail.com) writes:[color=blue]
> Some but not all of the dates are incorrect converted (incremented by 1
> day) with the following SQL statement.
>
> SELECT DISTINCT CONVERT(datetim e,cast(Returned ByDate AS int)) AS
> ReturnedByDate, CONVERT(varchar (10),ReturnedBy Date,104) AS
> LabelReturnedBy Date, ReturnedByDate FROM i2b_keytransact ionhistory
> ORDER BY ReturnedByDate ASC[/color]

Converting datetime to int is not a very bright idea. For some reason,
that comes with rounding, so hours in the afternoon gets round up to
the next day.

Stick to convering your datetime to character.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Incorrect Date Conversion - Help Please

On 28 Dec 2004 18:26:55 -0800, theintrepidfox@ hotmail.com wrote:
[color=blue]
>Some but not all of the dates are incorrect converted (incremented by 1
>day) with the following SQL statement.
>
>SELECT DISTINCT CONVERT(datetim e,cast(Returned ByDate AS int)) AS
>ReturnedByDate , CONVERT(varchar (10),ReturnedBy Date,104) AS
>LabelReturnedB yDate, ReturnedByDate FROM i2b_keytransact ionhistory
>ORDER BY ReturnedByDate ASC[/color]

Hi Martin,

What Erland says :-)

But if you somehow REALLY need the number of days since 19000101 in your
frontend, the following should prove more reliable:

SELECT DATEDIFF(day, '19000101', ReturnedByDate)

Best, Hugo
--

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