Oracle - format a date, Date Add

Here is a query to format a date

select distinct job_date, to_char(job_date, 'Dy DD-Mon-YYYY HH24:MI:SS') AS "job_date_text" from duhTable
order by job_date desc

"JOB_DATE" "job_date_text"
"05-JAN-07" "Fri 05-Jan-2007 19:00:44"
"05-JAN-07" "Fri 05-Jan-2007 08:00:37"
"03-JAN-07" "Wed 03-Jan-2007 10:46:35"
"29-DEC-06" "Fri 29-Dec-2006 08:00:38"


Here is a query to get data “older than 5 days”:

select *
from duhTable latest
  Left Outer Join
    (Select loanxid, job_date, closebid
     from duhTable earlier
     where job_date in (select max(x.job_date) from duhTable x
                        --where x.Job_Date <> (Select Max(z.job_date) from duhTable z))) Earlier
                        where x.Job_Date < (Select Max(z.job_date) - 5 from duhTable z))) Earlier
  On latest.loanxid = Earlier.loanxid
Where  latest.Job_Date in (Select Max(job_date) from duhTable)

 

Here is a query to get data “for a certain date”:

select distinct job_date from duhTable where trunc(job_date) = '12-MAR-07'

format date range SQL:

    Public Shared Function GetOracleDateRange(ByVal inStart_DateString As String, _
                            ByVal inEnd_DateString As String) As String
 
        Dim x As String = ""
        Dim theStart_DateString As String
        Dim theEnd_DateString As String
        Dim Start_Date As DateTime
        Dim End_Date As DateTime
        If inStart_DateString.Trim = "" Then
            theStart_DateString = "01/01/1980"
        Else
            Start_Date = Date.Parse(inStart_DateString.ToString)
            theStart_DateString = Start_Date.ToString("MM/d/yyyy")
        End If
        If inEnd_DateString = "" Then
            theEnd_DateString = "01/01/2080"
        Else
            End_Date = Date.Parse(inEnd_DateString.ToString)
            'End_Date = End_Date.AddDays(1)
            theEnd_DateString = End_Date.ToString("MM/d/yyyy")
        End If
        x = " between to_date('" & theStart_DateString & "','MM/DD/YYYY') and to_date('" & theEnd_DateString & "','MM/DD/YYYY')"
 
        Return x
    End Function
            Dim dateRangeString As String = portalDateRange.GetOracleDateRange(Start_DateString, End_DateString)
 
            Select Case sDateRangeType.ToLower
                Case "range"
                    where = " and ws.Date_Stamp " + dateRangeString
                Case "ytd"
                    where = " and trunc(ws.Date_Stamp,'yyyy') = trunc(sysdate,'yyyy')"
            End Select