Home » questions » Help with a SQL Statement where date of a record is 24 hours or more old?

Help with a SQL Statement where date of a record is 24 hours or more old?

4
2006-08-08 20:54:06, Category: Programming & Design
I need to figure out how to compare a column that is a varchar and change it to a date, then add in a different column that is also a varchar and convert it to a time (don't ask, bad DB design), and then see if the date is equal to or greater than 24 hours before today's date and current time. Please email me if you need clarification. THANK YOU IN ADVANCE AS THIS IS URGENT AND NEEDED BY TOMORROW EVENING!

Answers

  1. Rullie

    On 2006-08-09 07:53:27


    this kinda depends on the database you're using. IF it's mysql, you can convert the string into a timestamp, then extract the date and time out of that, stick them into the two columns, then use timediff to figure out the difference.

  2. Zhonghua

    On 2006-08-10 06:35:16


    I am a database development programer, you use some tools and learn sql(struct query language ). You can get one tool of Advanced SQL Builder at http://www.dlkj.net/sqlBuilder/ . My email is ymx@hdrj.net. OK?

  3. truth_teller

    On 2006-08-08 21:10:18


    try this, CONVERT (smalldatetime, + ) alternatively try casting CAST ( + ), smalldatetime)...you may have to add a space in between the fields as in + ' ' + hope this helps. To test this you can use DATEDIFF function see:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp

  4. Pands

    On 2006-08-08 21:51:36


    Lets say column1 as date (with format of 'mmddyyyy') and column2 as time portion (with format of hh24:mi:ss'). In Oracle, select (case when to_date(column1 || ' ' || column2, 'mmddyyyy hh24:mi:ss') > sysdate then 'Tomorrow' when to_date(column1 || ' ' || column2, 'mmddyyyy hh24:mi:ss') < sysdate then 'Today' else 'Not a good day') date_check from tablename;