March 17, 2009 at 3:21 pm
I have two fields, ArrivalDate(1/5/2009 12:00 AM) and ArrivalTime(1/1/1900 12:56:00 PM) both have time and date in the fields and is a display issue.
Im looking for SQL that would work for both TSQL and PLSQL that could either parse the date/ time out in each
If this is even possible. I dont care how ugly.
March 17, 2009 at 4:51 pm
[font="Verdana"]The only conversion in common across both SQL Server and Oracle is CAST(). That doesn't give you any formatting control. So unless the date format is set the same on both clients (not inconceivable), you will get different results. I certainly wouldn't rely on the format of a returned string from casting a datetime to a varchar.
In SQL Server, you can use CONVERT(), in Oracle you can use TO_CHAR() to get the necessary formatting control.
[/font]
March 17, 2009 at 10:35 pm
Bruce W Cassidy (3/17/2009)
[font="Verdana"]The only conversion in common across both SQL Server and Oracle is CAST(). That doesn't give you any formatting control. So unless the date format is set the same on both clients (not inconceivable), you will get different results. I certainly wouldn't rely on the format of a returned string from casting a datetime to a varchar.In SQL Server, you can use CONVERT(), in Oracle you can use TO_CHAR() to get the necessary formatting control.
[/font]
yes unfortunately i cannot do this client side, due to the scenerio on how its a bind situation etc. I have some work arounds, but its so close that all of the systems sql is working on both environments except for this one situation. I dont care of the results look different on the client, i rather the statment be the same. I am aware im suppose to use convert for SSE and to_char for orcl but i will play with Cast thank you.
March 20, 2009 at 3:21 am
xgcmcbain (3/17/2009)
Im looking for SQL that would work for both TSQL and PLSQL
Welcome to the exciting world of looking for SQL holly grail 🙂
What I have done in the past to solve this issue was resorting to a library of external functions.
Your core code is always the same but when facing a SQLServer/Oracle SQL dilemma core code calls a function.
The idea is to have two versions for each function, the first one for SQL Server and the second one for Oracle -named here in no particular order 😀
This solution allows you to have a single version of core code while solving specifics in external functions that are custom coded using the best SQL Server or Oracle solution depending on the version of each specific function 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply