Menu
I've spent hours searching the web for an answer to this question...
Here's what I currently have:
select * from order_header oh where tran_date = sysdate-1 
462
To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .
While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt . The value returned is always of datatype DATE , even if you specify a different datetime datatype for date . If you omit fmt , then date is truncated to the nearest day.
Use:
AND oh.tran_date BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE) - 1/86400 Reference: TRUNC
Calling a function on the tran_date means the optimizer won't be able to use an index (assuming one exists) associated with it. Some databases, such as Oracle, support function based indexes which allow for performing functions on the data to minimize impact in such situations, but IME DBAs won't allow these. And I agree - they aren't really necessary in this instance.
95
trunc(tran_date) = trunc(sysdate -1) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
