Solved

Oracle to PL/SQL conversion

Posted on 2002-05-02
4
571 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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

856 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