Solved

Need assistance with formatting the date in the output

Posted on 2012-03-26
11
588 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
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.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now