Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need assistance with formatting the date in the output

Posted on 2012-03-26
11
603 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:mtrout
11 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 300 total points
ID: 37768717
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...!!!!
0
 

Expert Comment

by:maqsood_ashiq
ID: 37768967
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37769025
>> 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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:Leo Torres
ID: 37769088
This is the easiest way I know how

SELECT convert(varchar, getdate(), 111)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37769115
>>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.
0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 37769180
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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37769306
>>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.
0
 
LVL 9

Expert Comment

by:keyu
ID: 37770114
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]


also try below link,

http://www.sql-server-helper.com/tips/date-formats.aspx
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37771052
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.
0
 

Author Comment

by:mtrout
ID: 37773016
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37773104
I'm a little confused.  Are you saying that everything is now working?  If so, please close this out by accepting the appropriate comments.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question