Home » SQL & PL/SQL » SQL & PL/SQL » declare variable to show date and tim in my parameter
declare variable to show date and tim in my parameter [message #353393] Mon, 13 October 2008 13:40 Go to next message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
How do you declare a variable to show Date and time in this format


AND (OI_CHARGES.PERF_PHYS_NBR IN ('1113','1210') AND (OI_CHARGES.DT_OF_SERVICE BETWEEN '20060701' AND '20070630'))

ORDER BY OI_CHARGES.DT_OF_SERVICE;


I know that I need to put a declare before my select statement to show the date field as : '20060701' '00:00:0000' AND '20070630''00:00:0000'

How do I do this?

Thanks!!
Re: declare variable to show date and tim in my parameter [message #353395 is a reply to message #353393] Mon, 13 October 2008 13:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Try look on TO_CHAR and format 'ss'.
Re: declare variable to show date and tim in my parameter [message #353451 is a reply to message #353393] Mon, 13 October 2008 22:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you looking for TIMESTAMP ?

Thumbs Up
Rajuvan.

[Updated on: Mon, 13 October 2008 23:22]

Report message to a moderator

Re: declare variable to show date and tim in my parameter [message #353454 is a reply to message #353393] Mon, 13 October 2008 23:10 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
Oracle provides a number of data types that can store dates (DATE, DATETIME, TIMESTAMP etc), however DATE is most commonly used.
declare 
x date;
begin
......

end;

[Updated on: Mon, 13 October 2008 23:11]

Report message to a moderator

Re: declare variable to show date and tim in my parameter [message #353644 is a reply to message #353454] Tue, 14 October 2008 08:09 Go to previous messageGo to next message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
So this is actually my code and it works. My goal is that when I do the between dates I am showing for example the date and time such as Between '20070107' '00:00:000' and '20080630' '00:00:000'
In other words to show from this date at midnite to that date at midnite. My OI_Charges.Dt_of_Service is format Number(9)
So I guess what I really need to know is how do I create a variable that will show as the time in this Between Statement?


SELECT
OI_CHARGES.PAT_NBR,
OI_CHARGES.DT_OF_SERVICE,
OI_CHARGES.PERF_PHYS_NBR,
PHYSICIAN.DISPLAY_NM,
OI_CHARGES.DIAG_CD,
DIAGNOSIS_CD.DIAG_CD_DESC,
OI_CHARGES.PROC_CD,
PC_DESC.PROC_CD_DESC_1,
OI_CHARGES.BILLED_AMT,
OI_CHARGES.PAT_PAID_AMT,
OI_CHARGES.PAT_NBR,
OI_CHARGES.DT_OF_SERVICE,
OI_CHARGES.PERF_PHYS_NBR,
PHYSICIAN.DISPLAY_NM,
OI_CHARGES.DIAG_CD,
DIAGNOSIS_CD.DIAG_CD_DESC,
OI_CHARGES.PROC_CD,
PC_DESC.PROC_CD_DESC_1,
OI_CHARGES.BILLED_AMT,
OI_CHARGES.PAT_PAID_AMT,
OI_CHARGES.PAT_ADJ_AMT,
OI_CHARGES.INS_PAID_AMT,
OI_CHARGES.INS_ADJ_AMT,
OI_CHARGES.OPEN_AMT

FROM OI_CHARGES

INNER JOIN PHYSICIAN
ON (OI_CHARGES.PERF_PHYS_NBR=PHYSICIAN.PHYS_NBR)

INNER JOIN DIAGNOSIS_CD
ON (OI_CHARGES.DIAG_CD=DIAGNOSIS_CD.DIAGNOSIS_CD)

INNER JOIN PC_DESC
ON (OI_CHARGES.PROC_CD=PC_DESC.PC_PROC_CD)

WHERE
(OI_CHARGES.PERF_PHYS_NBR IN ('1113','1210') )
AND (OI_CHARGES.DT_OF_SERVICE BETWEEN '20060701' AND '20070630')

ORDER BY
OI_CHARGES.DT_OF_SERVICE;
Re: declare variable to show date and tim in my parameter [message #353648 is a reply to message #353644] Tue, 14 October 2008 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Tue, 14 October 2008 08:28]

Report message to a moderator

Re: declare variable to show date and tim in my parameter [message #353650 is a reply to message #353648] Tue, 14 October 2008 08:23 Go to previous messageGo to next message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
So this is actually my code and it works. My goal is that when I do the between dates I am showing for example the date and time such as Between '20070107' '00:00:000' and '20080630' '00:00:000'
In other words to show from this date at midnite to that date at midnite. My OI_Charges.Dt_of_Service is format Number(9)
So I guess what I really need to know is how do I create a variable that will show as the time in this Between Statement?


Sorry for not posting correctly here is my code:

SELECT oi_Charges.Pat_nbr,
oi_Charges.dt_Of_Service,
oi_Charges.Perf_phys_nbr,
Physician.Display_nm,
oi_Charges.diag_cd,
Diagnosis_cd.diag_cd_desc,
oi_Charges.Proc_cd,
pc_desc.Proc_cd_desc_1,
oi_Charges.Billed_Amt,
oi_Charges.Pat_Paid_Amt,
oi_Charges.Pat_nbr,
oi_Charges.dt_Of_Service,
oi_Charges.Perf_phys_nbr,
Physician.Display_nm,
oi_Charges.diag_cd,
Diagnosis_cd.diag_cd_desc,
oi_Charges.Proc_cd,
pc_desc.Proc_cd_desc_1,
oi_Charges.Billed_Amt,
oi_Charges.Pat_Paid_Amt,
oi_Charges.Pat_Adj_Amt,
oi_Charges.Ins_Paid_Amt,
oi_Charges.Ins_Adj_Amt,
oi_Charges.Open_Amt
FROM oi_Charges
INNER JOIN Physician
ON (oi_Charges.Perf_phys_nbr = Physician.phys_nbr)
INNER JOIN Diagnosis_cd
ON (oi_Charges.diag_cd = Diagnosis_cd.Diagnosis_cd)
INNER JOIN pc_desc
ON (oi_Charges.Proc_cd = pc_desc.pc_Proc_cd)
WHERE (oi_Charges.Perf_phys_nbr IN ('1113',
'1210'))
AND (oi_Charges.dt_Of_Service BETWEEN '20060701'
AND '20070630')
ORDER BY oi_Charges.dt_Of_Service;
Re: declare variable to show date and tim in my parameter [message #353651 is a reply to message #353648] Tue, 14 October 2008 08:25 Go to previous message
LOgle0917
Messages: 10
Registered: February 2008
Location: Tennessee
Junior Member
So this is actually my code and it works. My goal is that when I do the between dates I am showing for example the date and time such as Between '20070107' '00:00:000' and '20080630' '00:00:000'
In other words to show from this date at midnite to that date at midnite. My OI_Charges.Dt_of_Service is format Number(9)
So I guess what I really need to know is how do I create a variable that will show as the time in this Between Statement?
I ran it through the SQL Formater. My Oracle is Version Oracle 10
Sorry for not posting correctly here is my code:

SELECT   oi_Charges.Pat_nbr,
         oi_Charges.dt_Of_Service,
         oi_Charges.Perf_phys_nbr,
         Physician.Display_nm,
         oi_Charges.diag_cd,
         Diagnosis_cd.diag_cd_desc,
         oi_Charges.Proc_cd,
         pc_desc.Proc_cd_desc_1,
         oi_Charges.Billed_Amt,
         oi_Charges.Pat_Paid_Amt,
         oi_Charges.Pat_nbr,
         oi_Charges.dt_Of_Service,
         oi_Charges.Perf_phys_nbr,
         Physician.Display_nm,
         oi_Charges.diag_cd,
         Diagnosis_cd.diag_cd_desc,
         oi_Charges.Proc_cd,
         pc_desc.Proc_cd_desc_1,
         oi_Charges.Billed_Amt,
         oi_Charges.Pat_Paid_Amt,
         oi_Charges.Pat_Adj_Amt,
         oi_Charges.Ins_Paid_Amt,
         oi_Charges.Ins_Adj_Amt,
         oi_Charges.Open_Amt
FROM     oi_Charges
         INNER JOIN Physician
           ON (oi_Charges.Perf_phys_nbr = Physician.phys_nbr)
         INNER JOIN Diagnosis_cd
           ON (oi_Charges.diag_cd = Diagnosis_cd.Diagnosis_cd)
         INNER JOIN pc_desc
           ON (oi_Charges.Proc_cd = pc_desc.pc_Proc_cd)
WHERE    (oi_Charges.Perf_phys_nbr IN ('1113',
                                       '1210'))
         AND (oi_Charges.dt_Of_Service BETWEEN '20060701'
                                               AND '20070630')
ORDER BY oi_Charges.dt_Of_Service;


[Edit MC: Use code tags as I edited your post]

[Updated on: Tue, 14 October 2008 08:29] by Moderator

Report message to a moderator

Previous Topic: Selecting with Condition
Next Topic: Minus question
Goto Forum:
  


Current Time: Sun Dec 04 04:46:50 CST 2016

Total time taken to generate the page: 0.09477 seconds