Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need assistance with formatting the date in the output

Posted on 2012-03-26
11
Medium Priority
?
623 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
[X]
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
11 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 1200 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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

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

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

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 77

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 77

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 77

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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