Date Column From Oracle - SQL Server Forums

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)

Date Column From Oracle

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-06 : 10:40:26

Hi,

I have a column in my database which came from Oracle database. In this Column I have date but the format is strange for me (Example: 105046).How can I convert this date to MM/DD/YY format in sql?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:31:02

Please give an two examples of what the numbers mean.

105046 means ...
...... means ...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:32:53

If 105046 means HOURS, then 105046 is most likely to be interpreted as 10 PM on December 25, 1981.
To be really sure we need two "measure points" and accurate interpretation.

E 12°55'05.25"
N 56°04'39.16"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-05-06 : 11:35:29

Here's a WAG in case 105046 means 2005-02-16:

DECLARE @mysterydate int
SET @mysterydate = 105046

SELECT DATEADD(day, @mysterydate % 1000, DATEADD(year, @mysterydate / 1000, 0))

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-06 : 11:37:48

No 105046 is date.I don't know what year and month and date it is but in Oracle they can convert it to mm/DD/YY with help of these 2 functions to_date() and to_char().

I don't know how to convert this number to valid mm/dd/yy format.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:44:52

Great!

What then, in Oracle, does 105046 represent using to_date() function?
When you post back result, please also post another value such as 107453 and the value to_date() function returns in Oracle.

E 12°55'05.25"
N 56°04'39.16"

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-06 : 11:46:23

Thank you so much Arnold it works.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:48:32

I see...
105 means 105 years after January 1st 1900 and then 046 means 46 days after New Year?

It would be interesting if someone here with access to Oracle could exec

SELECT TO_DATE(105046)

and post back the result.

E 12°55'05.25"
N 56°04'39.16"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-05-06 : 11:58:47

Hmm... on reflection, I think it's more likely that the day number is 1-based rather than 0-based. I.e. that I'm a day out and that the calculation should be:

SELECT DATEADD(day, @mysterydate % 1000 - 1, DATEADD(year, @mysterydate / 1000, 0))

quote:
Originally posted by Peso

I see...
105 means 105 years after January 1st 1900 and then 046 means 46 days after New Year?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 12:01:57

So 105000 means "January 1st 2005"? SELECT DATEADD(DAY, 105000 % 1000 , DATEADD(YEAR, 105000 / 1000, '19000101'))
Or 105001 means "January 1st 2005"? SELECT DATEADD(DAY, 105001 % 1000 , DATEADD(YEAR, 105001 / 1000, '18991231'))

E 12°55'05.25"
N 56°04'39.16"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-05-06 : 12:05:51

It must have been a vague recollection of this thread from, erm, almost 7 years ago:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11248

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-06 : 12:08:12

Arnold can you help me to find out how I can have the date formate like mm/dd/yy on that conversion?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-05-06 : 12:27:26

Well, converting something to a datetime value and formatting a datetime value as a string with a particular format are two independent things.

The latter, if done from SQL Server, is just a question of calling CONVERT with the right value of the 'style' parameter. The documentation for CAST and CONVERT shows the different formats supported.

DECLARE @mysterydate int
DECLARE @sqldate datetime
SET @mysterydate = 105046

SET @sqldate = DATEADD(day, @mysterydate % 1000 - 1, DATEADD(year, @mysterydate / 1000, 0))

SELECT CONVERT(varchar(8), @sqldate, 1)