help needed from Gurus to convert oracle date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help needed from Gurus to convert oracle date

Author

Topic

rayans1234
Starting Member

7 Posts

Posted - 2008-03-12 : 22:07:10

select to_char(INITIAL_LOAD_DATE,'dd-mon-yyyy hh:mi:ss') from trans

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-12 : 22:15:15

don't repost.it will take longer than 8 minutes for someone to help you out..

Do you just need to know how to select a datetime column from a table and format it? That should be done client side..

but check here:http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Poor planning on your part does not constitute an emergency on my part.

rayans1234
Starting Member

7 Posts

Posted - 2008-03-12 : 22:44:49

Wow, I didn't expect such a quick response. Amazing. Thanks guys.
However, I still haven't got the answer to my questions.
Can someone please translate the following oracle statements to sql server? Thanks.
Note: The link provided earlier didn't have the answer to my questions.
Also, I have researched a lot on web, but couldn't find the sql server date style for the oracle format dd-mon-yyyy hh24:mi:ss
That is really strange that Sql Server has a very few date formats as compared to Oracle and Postgresql.

SELECT to_date('24-08-2007 13:11:12','dd-mm-yyyy hh24:mi:ss'),to_date('24-aug-2007 13:11:12','dd-mon-yyyy hh24:mi:ss') from dual

select to_char(INITIAL_LOAD_DATE,'dd-mon-yyyy hh:mi:ss') from TRANS
Note: INITIAL_LOAD_DATE is a date column in TRANS table

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-12 : 23:21:01

You should read about the CONVERT function in SQL Server Books Online.

You might also find the link below helpful for more information about SQL Server datatime usage.
Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

The function on this link has a number of examples of producing dates in character formats that you might find helpful.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-13 : 16:35:59

You should also ask in an Oracle forum somewhere. These forums are specific to MS SQL Server.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-14 : 06:21:22

Also if you want to show formatted dates in front end application, better you format it there

Madhivanan

Failing to plan is Planning to fail

rayans1234
Starting Member

7 Posts

Posted - 2008-03-17 : 17:33:20

Thank everybody for your help.
However, I am so disappointed from this forum. I asked couple of simple questions and instead of getting the proper answers, I got advices from Gurus to look into online documents. I looked into the documents, searched through google but couldn't find what I need and that is why I posted my question on this forum. I have couple of queries in Oracle. I needed to translate them in Postgresql and Sql Server. Converting them in postgresql was a piece of cake. However, converting them in Sql Server, has proved to be hell of a job. I kind of started disliking Sql Server now because such a simple thing cannot be resolved (through documentation, forums etc).
Sql Server gives very few date styles (I don't know why these number 105, 130, 131 etc instead of date format strings such as dd/mmm/yyyy hh:mm:ss as are given in other databases such as Oracle and Postgresql) where as Oracle and Postgresql are not limited.
Anyway, I am still hopeful that there must be atleast one great Guru who will understand my questions and reply to the point with examples instead of suggesting me to read the documents.

Regards
Rayans

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-03-17 : 17:42:31

SELECT CONVERT(VARCHAR(10), GETDATE(), 101), CONVERT(VARCHAR(10), GETDATE(), 110), CONVERT(VARCHAR(20), GETDATE(), 120)

The third parameter tells what format to be. You should be able to find what each parameter (101, 110, 120, and so on) does online or in Books Online.

rayans1234
Starting Member

7 Posts

Posted - 2008-03-17 : 17:52:24

Thanks Van.
Probably I couldn't clarify my question. I am realy struggling with this date stuff in sql server. If you run the query below, you should expect
no records as the date on the left side is less than the current date. When I run it, I get one record. Strange.

select 1
where convert(datetime, '13/02/2007 11:00:00',131) > getDate()

I am using style 131. If this is not the right style then do you know which one? This is the closest style number that I could fine. Can you fix this query or tell me what mistake I am doing here?

Thanks in advance.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-03-17 : 17:58:23

Run this and your question will be answered...

select convert(datetime, '13/02/2007 11:00:00',131)

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-03-17 : 18:01:23

Try this:

select 1
where convert(datetime, '02/13/2007 11:00:00') > getDate()

rayans1234
Starting Member

7 Posts

Posted - 2008-03-17 : 18:07:23

I get 2568-12-04 11:00:00.000 which is weired.
So, how can I get what I want.

Works good in Oracle
select 1 from dual
where to_date('12/12/2007 15:30:30','dd/mm/yyyy HH24:mi:ss')>sysdate;

Works good in Postgresql
select 1
to_timestamp('12/12/2007 15:30:30','dd/mm/yyyy HH24:mi:ss') > current_timestamp;

Sql Server
Can you translate please?

rayans1234
Starting Member

7 Posts

Posted - 2008-03-17 : 18:09:45

forgot to put where in the Postgresql query
Works good in Postgresql
select 1
where to_timestamp('12/12/2007 15:30:30','dd/mm/yyyy HH24:mi:ss') > current_timestamp;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-18 : 03:00:06

<<
select 1
where convert(datetime, '13/02/2007 11:00:00',131) > getDate()
>>

You should express dates in YYYYMMDD HH:MM:SS format to avoid any ambuiguity

select 1
where '20070213 11:00:00' > getDate()

or

set dateformat dmy
select 1
where convert(datetime, '13/02/2007 11:00:00') > getDate()
set dateformat mdy

Madhivanan

Failing to plan is Planning to fail

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-03-18 : 10:33:57

Also, you put a 3rd parameter on your convert. Leave that off.

You have:
select 1
where convert(datetime, '13/02/2007 11:00:00',131) > getDate()

Should be:
select 1
where convert(datetime, '02/13/2007 11:00:00') > getDate()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources