Solved

Oracle to PL/SQL conversion

Posted on 2002-05-02
4
575 Views
Last Modified: 2010-04-17
I am currently working on the conversion of PL/SQL script to T-SQL. I have certain issues that have come up correspondingly.

The To_char function in Oracle can be converted to T-sql by using convert. But Convert needs three argumemnts when date has to be converted to character.

for example:

to_char(sysdate,'dd-mon-yyyy')in Oracle can be replaced with Convert(char,getDate(),106). Here 106 represents the style of the date format 'dd-mon-yyyy'.

The problem is I have a pl/sql script with the following date formats:
'dd'
'MMYYHH24MISS'

I would like to know what are the corresponding Style numbers for the same in T-sql.

Thanks In Advance.

0
Comment
Question by:nibeditas
[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
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6988204
For 'dd':
select convert( char(2), GetDate(), 103)
Note that you need to convert to char(2), this will strip of the rest of the style 103 (dd/mm/yy). Now, you could by this also use other styles ...

for 'MMYYHH24MISS':
This one is more tricky, as omitting the separators and the spaces etc needs to be done:

select substring( replace( replace(replace( convert (varchar(19), getdate(), 120) , ':', ''), '-', ''), ' ', '') , 5,15)

Now, as you can see, the SQL Server functions convert are not that flexible as Oracle's to_char function...

CHeers






0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6988209
Note that there exist dedicated topics to MS SQL Server and oracle here:
http://www.experts-exchange.com/jsp/qList.jsp?ta=mssql
http://www.experts-exchange.com/jsp/qList.jsp?ta=oracle

CHeers
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7515086

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: angelIII
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Expert Comment

by:Jgould
ID: 7548142
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.

734 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