By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Incorrect Date Conversion - Help Please

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)

Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
(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
Jul 23 '05 #4

P: n/a
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)
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.