How do I append a time to this date in Oracle using ASP?

I have a date field in Oracle that returns a date as "3/30/2011" with no time if it was submitted at midnight.  If a date is entered at any other time it works fine for example "3/30/2011 7:00:00 AM"

I need a way to check if a date has no time at the end and then append the time like so using classic ASP: "3/30/2011 12:00:00 AM"

Any help would be appreciated.
Donnie WalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Oracle dates have time built in.  You can only separate them on query.  You can get any format in and out by explicitly using to_char and to_date.  These allow about any format mask you can think up.

What is your ASP code?
0
Donnie WalkerAuthor Commented:
I understand that. But the data being returned from the Oracle database (when it is entered at midnight) is "3/30/2011". Any other dates (from the same database and field) show up as "3/30/2011 7:00:00 AM".

So, when I display these records on my ASP page I want it to look for any date that does not have a time and append "12:00:00 AM" to the date on the page.
0
slightwv (䄆 Netminder) Commented:
If you force the output to a string, it shouldn't drop the time.

Select to_char(dateCol,'MM/DD/YYYY HH:MI:SS AM') from table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Donnie WalkerAuthor Commented:
is that an Oracle or an ASP thing?

can I do something like this?

vOracleDate = RS("DATE")
vDate = Select to_char(vOracleDate,'MM/DD/YYYY HH:MI:SS AM')
0
slightwv (䄆 Netminder) Commented:
to_char is an Oracle function.  That's why I asked to see your code.  To see how you are accessing the database.
0
Donnie WalkerAuthor Commented:
Here is the query:

        SET RS = server.CreateObject("ADODB.Recordset")
            sSQL = "SELECT EWM_WORK_ORDERS.WORK_ORDER_NO, EWM_WO_STEP_SCHEDULES.STEP_NO, EWM_WO_STEP_SCHEDULES.SCHED_DATE, TSW_CODES_5.USER_CODE AS AREA, "
            sSQL = sSQL & "EWM_WORK_ORDERS.ASSET_NAME, EAM_ASSETS.ASSET_DESC, EWM_WORK_ORDERS.REPAIR_TAG_NO, TSW_CODES_1.USER_CODE AS CRAFT, TSW_CODES_6.USER_CODE AS CREW, "
            sSQL = sSQL & "TSW_CODES_3.USER_CODE AS SHIFT, EWM_WO_STEP_SCHEDULES.SCHED_MEN, EWM_WO_STEP_SCHEDULES.SCHED_HRS, EWM_WO_STEPS_TEXT.STEP_DESC, "
            sSQL = sSQL & "EWM_WORK_ORDERS.WO_DESC, EWM_WORK_ORDERS.WO_REF_NO, TSW_CODES.DESCRIPTION, TSW_CODES_4.DESCRIPTION AS PRIORITY, EWM_WORK_ORDERS.REQRD_DATE, "
            sSQL = sSQL & "TSW_PERSONNEL.FIRST_NAME, TSW_PERSONNEL.LAST_NAME, EWM_WORK_ORDERS.CODE_COMBO_OVERLAY  FROM EMPAC.EAM_ASSETS EAM_ASSETS, "
            sSQL = sSQL & "EMPAC.EWM_WO_STEP_SCHEDULES EWM_WO_STEP_SCHEDULES, EMPAC.EWM_WO_STEPS_TEXT EWM_WO_STEPS_TEXT, EMPAC.EWM_WORK_ORDERS EWM_WORK_ORDERS, "
            sSQL = sSQL & "EMPAC.TSW_CODES TSW_CODES, EMPAC.TSW_CODES TSW_CODES_1, EMPAC.TSW_CODES TSW_CODES_2, EMPAC.TSW_CODES TSW_CODES_3, EMPAC.TSW_CODES TSW_CODES_4, "
            sSQL = sSQL & "EMPAC.TSW_CODES TSW_CODES_5, EMPAC.TSW_CODES TSW_CODES_6, EMPAC.TSW_PERSONNEL TSW_PERSONNEL  WHERE EWM_WORK_ORDERS.WO_STATUS_ID = TSW_CODES.CODE_ID AND "
            sSQL = sSQL & "EWM_WORK_ORDERS.WORK_ORDER_NO = EWM_WO_STEP_SCHEDULES.WORK_ORDER_NO AND EWM_WO_STEP_SCHEDULES.CRAFT_ID = TSW_CODES_1.CODE_ID AND EWM_WORK_ORDERS.DEPARTMENT_ID = "
            sSQL = sSQL & "TSW_CODES_2.CODE_ID AND EWM_WORK_ORDERS.PLANNER = TSW_PERSONNEL.EMP_NO(+) AND EWM_WO_STEP_SCHEDULES.SHIFT_ID = TSW_CODES_3.CODE_ID AND "
            sSQL = sSQL & "EWM_WORK_ORDERS.PRIORITY_ID = TSW_CODES_4.CODE_ID(+) AND EWM_WO_STEP_SCHEDULES.WORK_ORDER_NO = EWM_WO_STEPS_TEXT.WORK_ORDER_NO AND EWM_WO_STEP_SCHEDULES.STEP_NO = "
            sSQL = sSQL & "EWM_WO_STEPS_TEXT.STEP_NO AND EWM_WORK_ORDERS.ASSET_NO = EAM_ASSETS.ASSET_NO(+) AND EAM_ASSETS.AREA_ID = TSW_CODES_5.CODE_ID(+) AND EWM_WO_STEP_SCHEDULES.CREW_ID = "
            sSQL = sSQL & "TSW_CODES_6.CODE_ID AND ((TSW_CODES_2.DESCRIPTION Like '"& vDept &"') AND (EWM_WO_STEP_SCHEDULES.SCHED_DATE >= to_date('"& vStartDate &"','MM/DD/YYYY') AND EWM_WO_STEP_SCHEDULES.SCHED_DATE< to_date('"& vEndDate &"','MM/DD/YYYY')+1)) "
            sSQL = sSQL & " ORDER BY EWM_WO_STEP_SCHEDULES.SCHED_DATE, EWM_WORK_ORDERS.WORK_ORDER_NO, EWM_WO_STEP_SCHEDULES.STEP_NO"'

      Set RS = oConnMTV.Execute(sSQL)


The EWM_WO_STEP_SCHEDULES.SCHED_DATE is what I think you need?
0
Donnie WalkerAuthor Commented:
Ok, I tried this:

to_char(EWM_WO_STEP_SCHEDULES.SCHED_DATE,'MM/DD/YYYY HH:MI:SS AM') AS vSchedDate, EWM_WO_STEP_SCHEDULES.SCHED_DATE,

and vSchedDate shows as nothing.

I have the second EWM_WO_STEP_SCHEDULES.SCHED_DATE there because I need it for a date calculation and when the "to_char" is there it converts it to text.

what am I doing wrong?
0
slightwv (䄆 Netminder) Commented:
That should work.  Not sure what else to try.

I'm away from work and don't have access to a real machine to test anything.

You can click the Request Attention link and ask a Moderator to find more experts.
0
Donnie WalkerAuthor Commented:
That did work. My mistake I misspelled the value. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.