Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

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
0
mtrout
Asked:
mtrout
1 Solution
 
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...!!!!
0
 
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.
0
 
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

SELECT convert(varchar, getdate(), 111)
0
 
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.
0
 
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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now