Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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
 
Posted on Wednesday, January 10, 2007 1:12 PM Dates | Back to top


Comments on this post: Oracle - format a date, Date Add, query by date

# re: Oracle - format a date, Date Add, query by date
Requesting Gravatar...
www.jfffff.com DJ舞曲 免费音乐 电影网站 明星娱乐 都市情感故事

jiaoyou.jfffff.com交友

www.jfewww.cn
Left by asdfas on Oct 15, 2008 4:59 PM

# re: Oracle - format a date, Date Add, query by date
Requesting Gravatar...
I'm looking for information about it, because I must write an essay on that subject. Thank you for so nice article, I found here a lot of useful details.
Left by terry on Jun 23, 2010 8:19 AM

# re: Oracle - format a date, Date Add, query by date
Requesting Gravatar...
I'm looking for information about it, because I must write an essay on that subject. Thank you for so nice article, I found here a lot of useful details.
Left by floral canvas on Jun 10, 2011 6:20 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net