Judging from your output it looks like you have defined START_DATE as a timestamp. If it were a regular date Oracle would be able to handle the implicit conversion. But as it isn’t you need to explicitly cast those strings to be dates. SQL> alter session set nls_date_format=”dd-mon-yyyy hh24:mi:ss” 2 / Session altered. SQL> SQL> … Read more
The WHERE clause is misplaced, it has to follow the table references and JOIN operations. Something like this: FROM tartikel p1 JOIN tartikelpict p2 ON p1.kArtikel = p2.kArtikel AND p2.nNr = 1 WHERE p1.dErstellt >= DATE(NOW() – INTERVAL 7 DAY) ORDER BY p1.kArtikel DESC EDIT (three plus years later) The above essentially answers the question … Read more
Another way to do it, without changing the whole computer time is using the setenv command like this : Sys.setenv(TZ=’GMT’)
You want to use interval and current_date: select * from books where returned_date > current_date – interval ‘7 days’ This would return data from the past week including today. Here’s more on working with dates in Postgres.
MySQL doesn’t have recursive functionality, so you’re left with using the NUMBERS table trick – Create a table that only holds incrementing numbers – easy to do using an auto_increment: DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Populate the table … Read more
Use DATEADD in your WHERE clause: … WHERE date < DATEADD(day, -30, GETDATE()) You can also use abbreviation d or dd instead of day.
function leapYear(year) { return ((year % 4 == 0) && (year % 100 != 0)) || (year % 400 == 0); }
You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on. SQL> select oldest – creation from my_table; If your date is stored as character data, you have to convert it to a date type first. SQL> select 24 * (to_date(‘2009-07-07 22:00’, ‘YYYY-MM-DD … Read more
Yes: TIME_CREATED contains a date and a time. Use TRUNC to strip the time: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TRUNC(TIME_CREATED) = TO_DATE(’26/JAN/2011′,’dd/mon/yyyy’) UPDATE: As Dave Costa points out in the comment below, this will prevent Oracle from using the index of the column TIME_CREATED if it exists. An alternative approach without this problem is … Read more