Need to automate date value in the equal to statement

by Justin
(Akron, Ohio, USA)

Hi I am currently running a query that looks at the jobs that ran and sees if they completed. It currently looks at the jobs for todays date like this and trunc(job.start_date_time) = '2-SEP-2013' ................ I currently have to go in and change the = to date everyday, is there a way to change that so it looks at the current date in that same format?

You'll be pleased to hear that there is a a way to do this automatically in Oracle and that is to use the SYSDATE function which returns the current system date and time of the computer that the database is running on (i.e from the host, not the client for multi-tier systems).

The function returns a value of type DATE and the exact format depends on the value of the NLS_DATE_FORMAT initialization parameter. You can also override the default format by wrapping the SYSDATE function in TO_CHAR function and explicitly specifying the format you want. See the Oracle Database SQL Language Reference 11g Release 2 (11.2) for more details.

in this particular case therefore we can simplify the WHERE clause of the original query and change it from this:

WHERE trunc(job.start_date_time) = '2-SEP-2013'

to this:

WHERE trunc(job.start_date_time) = trunc(SYSDATE)

One thing to note when using TRUNC on dates is that Oracle doesn't as one might expect just return a date without any time component i.e '02-sep-2013' instead it converts the time to midnight of the day in question so what is actually returned is the value '02-sep-2013 00:00'. As mentioned previously you can change the format of the date value returned using the to_char function so another way to compare dates ignoring the time component would be to do this:

WHERE to_char(job.start_date_time,'mm-dd-yyyy') = to_char(SYSDATE,'mm-dd-yyyy')

See our SQL tutorials for more help with SQL.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.