#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8

    Combine Date and time columns


    Okay so I have a date column and a time column.

    I'd like to combine the two columns together in my asp coding or beforehand. how can I combine the two together?

    Right now when I get data from my database with the two columns I get results on my asp page like this:

    12-18-07(from date colum) 12-19-07 5:09pm (from time column)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    try DATE_ADD(datecol, INTERVAL timecol HOUR_SECOND)

    Comments on this post

    • Axweildr agrees : congrats on TipMaster of the week ....
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    try DATE_ADD(datecol, INTERVAL timecol HOUR_SECOND)
    Thanks!

    Where does this code go?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    in the SELECT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    in the SELECT statement
    So in my wonderfully complicated SQL statement I would place that code...at the end of the statement? Or does it need to happen before....or does it matter?

    strSQL = "SELECT p1.source, p1.message, p1.cdate, p1.ctime FROM alarms2 as p1 WHERE p1.ctime = (SELECT max(ctime) FROM alarms2 WHERE source=p1.source AND cdate=p1.cdate) AND message LIKE '%Node Down%' OR message LIKE '%If Down%' OR message LIKE '%Address Down%' OR message LIKE '%Connection Down%' ORDER BY cdate DESC, ctime DESC LIMIT 100"
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    change this --

    SELECT p1.source, p1.message, p1.cdate, p1.ctime

    to this --

    SELECT p1.source, p1.message, DATE_ADD(p1.cdate, INTERVAL p1.ctime HOUR_SECOND) as cdatetime


    by the way, you also have a precedence problem in your ANDs and ORs

    change this --

    WHERE p1.ctime = (SELECT max(ctime) FROM alarms2 WHERE source=p1.source AND cdate=p1.cdate) AND message LIKE '%Node Down%' OR message LIKE '%If Down%' OR message LIKE '%Address Down%' OR message LIKE '%Connection Down%'

    to this --

    WHERE p1.ctime = (SELECT max(ctime) FROM alarms2 WHERE source=p1.source AND cdate=p1.cdate) AND ( message LIKE '%Node Down%' OR message LIKE '%If Down%' OR message LIKE '%Address Down%' OR message LIKE '%Connection Down%' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    change this --

    SELECT p1.source, p1.message, p1.cdate, p1.ctime

    to this --

    SELECT p1.source, p1.message, DATE_ADD(p1.cdate, INTERVAL p1.ctime HOUR_SECOND) as cdatetime
    Alright I'll give it a swing...thanks!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    change this --
    SELECT p1.source, p1.message, DATE_ADD(p1.cdate, INTERVAL p1.ctime HOUR_SECOND) as cdatetime
    Those changes didn't work. It threw this error:

    ADODB.Recordset error '800a0cc1'

    Item cannot be found in the collection corresponding to the requested name or ordinal.

    /currentalarm/overallstatus2.asp, line 21

    Here is the code we're dealing with line 21 is in red:

    Sub showNodes
    strSQL = "SELECT p1.source, p1.message, p1.cdate, p1.ctime FROM alarms2 as p1 WHERE p1.ctime = (SELECT max(ctime) FROM alarms2 WHERE source=p1.source AND cdate=p1.cdate) AND message LIKE '%Node Down%' OR message LIKE '%If Down%' OR message LIKE '%Address Down%' OR message LIKE '%Connection Down%' ORDER BY cdate DESC, ctime DESC LIMIT 100"
    Set objRS = siteConn.Execute(strSQL)
    If objRS.EOF Then
    response.write Space(10) & "<tr>" & vbCrLf
    response.write Space(12) & "<td colspan=""4"" class=""style 3"" font color=""red"" align=""left"">Currently No Alarms!</td>" & vbCrLf
    response.write Space(10) & "</tr>" & vbCrLf
    Else
    Do While Not objRS.EOF
    response.write Space(10) & "<tr>" & vbCrLf
    response.write Space(12) & "<td class=""style9 style10"">" & objRS("cDate") & "</td>" & vbCrLf
    response.write Space(12) & "<td class=""style9 style10"">" & objRS("cTime") & "</td>" & vbCrLf
    response.write Space(12) & "<td class=""style9 style10"">" & objRS("source") & "</td>" & vbCrLf
    response.write Space(12) & "<td class=""style9 style10"">" & objRS("message") & "</td>" & vbCrLf
    response.write Space(10) & "</tr>" & vbCrLf
    objRS.MoveNext
    Loop
    End If
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    a little bird told me that you should be Response.Writing objRS("cdatetime") instead

    assuming that you tried what i suggested in post #6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    a little bird told me that you should be Response.Writing objRS("cdatetime") instead

    assuming that you tried what i suggested in post #6
    I did try the suggested post...just posted the wrong code...but you're right about the "objRS(cdatetime) I'll give that a try...missed that one.
    Last edited by WhiteClaw48; December 20th, 2007 at 09:55 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Posts
    54
    Rep Power
    8
    Originally Posted by r937
    a little bird told me that you should be Response.Writing objRS("cdatetime") instead

    assuming that you tried what i suggested in post #6
    Made the fix...and it seems to have done the trick! Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo