Incorrect Date Conversion - Help Please

[theintrepidfox]

Dear Group

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

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
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 LabelReturnedByDate 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]

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

SELECT DISTINCT cast(@ReturnedByDate AS real) AS Y,
cast(@ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@ReturnedByDate,104) AS
LabelReturnedByDate, @ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC
select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

[xtremeSQL]

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

SELECT DISTINCT cast(@ReturnedByDate AS real) AS Y,
cast(@ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@ReturnedByDate,104) AS
LabelReturnedByDate, @ReturnedByDate
-- FROM i2b_keytransactionhistory 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]

(th************@hotmail.com) writes:

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

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
ORDER BY ReturnedByDate ASC

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, es****@sommarskog.se

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

[Hugo Kornelis]

On 28 Dec 2004 18:26:55 -0800, th************@hotmail.com wrote:

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

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
ORDER BY ReturnedByDate ASC

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)