convert two columns that are chr(8) to datetime -- sql server programming

ripper951
2/14/2007 7:54:03 AM

If you look at the code I posted that is exactly what I am trying to do with
the code I have.

This is part of a select statement that builds a recordset.

here is the complete select statement which in the end will be part of a
stored procedure.

SELECT CAST(CONVERT(datetime,CDATE,112) + ' ' + CONVERT(datetime, CTIME,108)
AS datetime) AS TransDate,CallType AS TransType,charge AS amount FROM
vwCD_BtnGrp_1 cd WHERE calltype = '2' AND completed = '1' AND pin like
'00000481728069%' UNION

[quoted text, click to view]

ripper951
2/14/2007 8:43:25 AM

Solved my own problem. After thinking about the error message I realized that
it was complaining more or less that the date was exceeding the limits of
the date time datatype. which made me look at the time format of CTIME which
was 1638, which was 24 hour format but doesnt containd a colon. So I added
one. Here is the resulting/working code/

CAST(CONVERT(datetime,CDATE,112) + ' ' + CONVERT(datetime, Left(CTIME,2)+
':' + Right(CTIME,2),108) AS datetime)

[quoted text, click to view]

ripper951
2/14/2007 10:01:03 AM

I guess something got lost in the translastion.

I am taking two columns CDATE = '20070208' and CTIME = '16:38' both are
char(8) and putting them together to create a string that = '20070208
16:38:00:00' then changing its type from string to datetime.

I can't put these into variables as it a changing value each time a record
is read.
That may be ok for examples, but I can't I get them into variables in the
middle of a running select statement.

I am not following what you are saying, but it basicly seems like you are
saying that if MS ever decides to change the datatype datetime I am going to
have problems.

No disrespect but I dont think I know any developers that sit there and
think about all the possible things MS might change when developing.

I am sure what you are saying holds water but I am just not understanding
the point you are trying to get across.

[quoted text, click to view]

ripper951
2/14/2007 10:11:07 AM

No, what I am trying to do is add two elements together THEN changing them to
datetime.

[quoted text, click to view]

ML
2/14/2007 10:38:45 AM

Take a good look at your query again, please. You CONVERT character values to
datetime, add them up, then CAST the result (which is already of the datetime
type) to datetime.

ML

---

ripper951
2/14/2007 10:43:00 AM

Your absolutely right and I imagine that was what Tibor was trying to tell me.

That was actually a copy and paste of an example I got from another post
here in this message group and I just changed the values to mine.

So I just need to take out the conversion to datetime and first add the two
together then convert the whole thing to datetime. Correct??

[quoted text, click to view]

ML
2/14/2007 11:03:05 AM

[quoted text, click to view]

I guess so, but only if the time portion contains a colon as a separator
between the hour and the minute values.

I.e.:
This will work as expected:

cast('20070208' + ' ' + '16:38' as datetime)

This won't:

cast('20070208' + ' ' + '1638' as datetime)

I assume you'll test this against a representative quantity of known values
before using it in production.

ML

---

ripper951
2/14/2007 11:53:15 AM

Yes this is what I came up with after doing some cleanup.

CAST(CDATE + ' ' + Left(CTIME,2)+ ':' + Right(CTIME,2)AS datetime) AS
TransDate

Do you see anything wrong with this?

[quoted text, click to view]

Tibor Karaszi
2/14/2007 6:01:24 PM

[quoted text, click to view]

Why do you say that? Below is the SELECT list of the query you posted:

SELECT CAST(CONVERT(datetime,CDATE,112) + ' ' + CONVERT(datetime, CTIME,108) AS datetime)

Let's replace the columns with variables:
DECLARE @d varchar(8), @t varchar(4)
SET @d = '20070208' SET @t = '1638'
SELECT CAST(CONVERT(datetime,@d,112) + ' ' + CONVERT(datetime, @t,108) AS datetime)

Compare above with my suggestion:

SELECT CAST(@d + ' ' + LEFT(@t, 2) + ':' + RIGHT(@t, 2) AS datetime)

Let's examine your expresson. Take for instance: CONVERT(datetime,@d,112) . This will product a
variable of the *datetime* datatype. Say you now have, as datetime: 2004-05-23 14:23:58. You now
want to use the + operator with a string. Datatype precedence say that the string will be converted
to datetime. If you run below, you will see that a string with a single space will be converted to
the datetime value 1900-01-01 00:00:00:
SELECT CAST(' ' AS datetime)

And finally, you have: CONVERT(datetime, @t,108). So, you try to convert a string looking like
'1628' to datetime, using conversion code 108. Conversion code 108 expects something like: 14:53:12.
This *does not* match '1628'. So it fails here.

But even if abovet would be OK, you would not have something like 1900-01-01 16:58:00. So, looking
at your query, and the parts you have inside your CAST, you would have something like:

CAST(dtval1 + dtval2 + dtval3 AS datetime). I.e., you would add three datetime values. Adding
datetime values using the + operator is a pretty meaningless thing. Try below, for instance:
SELECT GETDATE() + GETDATE()

See the result? And you try to add three datetime values, which wouldprobably give you something
like year 2221, not what you expected. So:

[quoted text, click to view]

If you read my code carefully, you will find big differenced between your code and my code. Did you
try my suggestion? :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Tibor Karaszi
2/14/2007 6:06:17 PM

I strongly suggest you read the post I just posted. What you are doing is converting a bunch of
elements to datetime and adding them together. This relies on the internal representation of the
datetime datatype. This is two integers, one for date and one for time. Take the ' ' string, for
example. This will be, as datetime 1900-01-01 00:00:00, which just happens to mean that each part is
internally represented as 0 (the internal ints). Now, consider if MS decides to change the internal
representation of the datetime datatype in a future version. I strongly suggest you use the
documented and supported functionality in the TSQL language...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Tibor Karaszi
2/14/2007 9:45:38 PM

[quoted text, click to view]

Thats should be fine assuming your values do follow that pattern. Btw, compare to my original
suggestion:

CAST(CDATE + ' ' + Left(CTIME,2)+ ':' + Right(CTIME,2)AS datetime)

SELECT CAST(@d + ' ' + LEFT(@t, 2) + ':' + RIGHT(@t, 2) AS datetime)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Tibor Karaszi
2/14/2007 9:54:46 PM

[quoted text, click to view]

I didn't suggest that. My usage of variables was for demonstration purposes (so we have some code we
all can execute), and the intent was for you to replace the variables with your column names. I see
now that you have ended up with the very same technique as I suggested in my original reply.

[quoted text, click to view]

I fail to see why you would rely on some undocumented technique (or vaguely documented) when there
exist perfectly fine and documented methods of accomplishing your task. In then end, it is of course
up to you what path you choose, I just volonteer my free time, trying to help out here. I have no
alterior motives. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

ML
2/15/2007 8:28:44 AM

I guess that 'very much alike' may be an understatement. ;)

ML

---

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : convert two columns that are chr(8) to datetime