Need assistance with formatting the date in the output

Hello,  

I could use some assistance please with formatting the date in the output of an SQL query.  I am able to do what I want, (change column names,etc), but somehow, I am not having much luck on the formatting side.  Using the replace function, from what I was able to locate, gave me the closest results.  I want to change the format of one column from mm/dd/yyyy to yyyy/mm/dd.  What I'm getting is dd-JUN-yy.  The column I want to format is 'Name of Period'.

The query:
select distinct scv_course_act.course_id as "Course Number",scv_course_act.offering_id as "Section ID", scv_course_act.title as "Course Name",
crafts.nm_cft as "Department Name",replace (scv_course_act.start_date,'mm/dd/yyyy','yyyymmdd') as "Name of Period",
null as "Year",scv_instr_assign.person_no as "Instructor Identifier",null as "Course Designation",
scv_course_act.domain as "Course Type", null as "Program",null as "Cross-list Code",null as "University Code",scv_course_act.start_date as "Start Date", scv_course_act.end_date as "End Date"
from
scv_course_act
join crafts
on
scv_course_act.cur_craft=CRAFTS.CD_CFT
join
SCV_INSTR_ASSIGN
on
scv_course_act.offering_id=SCV_INSTR_ASSIGN.OFFERING_ID
where scv_course_act.start_date between to_date ('2010/12/31','yyyy/mm/dd') and to_date ('2013/01/01','yyyy/mm/dd')
and
scv_course_act.offering_id=scv_instr_assign.offering_id

I've also  tried yyyyMmDd in the format, thinking that would help.  It did not.

thank you for your assistance
Example-Date-Test.xls
mtroutAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Wasim Akram ShaikCommented:
i hope the column which you are using is of varchar then

use this

to_char (scv_course_act.start_date,'yyyy/mm/dd').,

this should work for you...!!!!

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
maqsood_ashiqCommented:
If your column type is DateTime then use the following:

CONVERT(VARCHAR(20), scv_course_act.start_date, 111)


If your column type is Varchar then use the following:

CONVERT(VARCHAR(20), CAST(scv_course_act.start_date as DATETIME), 111)

In this way you will get the date as yyyy/mm/dd.
slightwv (䄆 Netminder) Commented:
>> I want to change the format of one column

If it is a date then the post by wasimibm is correct.  Dates have no 'format'.  They are in an internal format (documented if you look it up).  The DD-MON-YY format is 'default'.  This is can be over-ridden by using to_char, described above or with NLS_DATE_FORMAT.

>>i hope the column which you are using is of varchar then

If it is varchar2 like mentioned above the SQL posted will not work.  It will try an implicit data conversion but will likely fail.  You will need to convert the string to a date first using TO_DATE.  Then convert it back into a string with TO_CHAR:



>>CONVERT(VARCHAR(20), scv_course_act.start_date, 111)

Since this was posted in an Oracle zone, I have to assume Oracle syntax.   This will not work in Oracle.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Leo TorresSQL DeveloperCommented:
This is the easiest way I know how

SELECT convert(varchar, getdate(), 111)
slightwv (䄆 Netminder) Commented:
>>SELECT convert(varchar, getdate(), 111)

Experts,

It was asked in an Oracle zone and used the syntax "start_date between to_date ('2010/12/31','yyyy/mm/dd')".

I believe that makes this an Oracle question.  Please stop posting SQL Server syntax.
Leo TorresSQL DeveloperCommented:
If that is the case then try


select to_char(some_date, 'yyyy-mm-dd hh24:mi:ss') my_date
  from some_table;

Open in new window

slightwv (䄆 Netminder) Commented:
>>If that is the case then try

Already been posted and besides, mtrout already posted the format they wanted it in and 'yyyy-mm-dd hh24:mi:ss' is nowhere close.

Re: " I want to change the format of one column from mm/dd/yyyy to yyyy/mm/dd. "

Before you post a TO_CHAR format for that, please read the previous posts.  We aren't sure if the column is an actual date or varcahr2 data type.  Even when that is provided, the solution has also been posted.
keyuCommented:
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]


also try below link,

http://www.sql-server-helper.com/tips/date-formats.aspx
slightwv (䄆 Netminder) Commented:
keyu,

Did you not see my post above?  This is Oracle.

Even if it was SQL Server, your post is a duplicate of a previous post.
mtroutAuthor Commented:
Hello,
I lost the previous comment I wrote.  First, Thank you everyone for reqponding so quickly.  the data type is defined as Date and I did not menition that earlier and I apologize for that.  I had initially tried the website:  http://www.sql-server-helper.com/tips/date-formats.aspx
before contacting Experts exchange and used a different number 112, and it did not work.  When I tried:  CONVERT(VARCHAR(20), scv_course_act.start_date, 111)
it did not work and I received an error.  I did try to_char(scv_course_act.start_date,'yyyy/mm/dd') and I received what I was expecting.  I removed the slashed.  I should have written that, but forgot to remove the slashes in my explaination of the error.  I have included the example results and I have listed the new text below.  Thank you again Everyone!!!

select distinct scv_course_act.course_id as "Course Number",scv_course_act.offering_id as "Section ID", scv_course_act.title as "Course Name",
crafts.nm_cft as "Department Name",to_char (scv_course_act.start_date,'yyyymmdd')as "Name of Period",
--replace (scv_course_act.start_date,'mm/dd/yyyy','yyyymmDd') as "Name of Period",
null as "Year",scv_instr_assign.person_no as "Instructor Identifier",null as "Course Designation",
scv_course_act.domain as "Course Type", null as "Program",null as "Cross-list Code",null as "University Code",scv_course_act.start_date as "Start Date", scv_course_act.end_date as "End Date"
from
scv_course_act
join crafts
on
scv_course_act.cur_craft=CRAFTS.CD_CFT
join
SCV_INSTR_ASSIGN
on
scv_course_act.offering_id=SCV_INSTR_ASSIGN.OFFERING_ID
where scv_course_act.start_date between to_date ('2010/12/31','yyyy/mm/dd') and to_date ('2013/01/01','yyyy/mm/dd')
and
scv_course_act.offering_id=scv_instr_assign.offering_id
Example-Date-Test-Working.xls
slightwv (䄆 Netminder) Commented:
I'm a little confused.  Are you saying that everything is now working?  If so, please close this out by accepting the appropriate comments.
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
DB Reporting Tools

From novice to tech pro — start learning today.