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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-07 : 09:28:38
|
| Mohammed writes "I am using sql database with ERP JDEDWARDS ONEWORLD I Tried to make datawearhouse but i face problem on date jdeoneworld stored date as julian date in sql how i can converted in sql in normal date mm/dd/yythanks in advance" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-10 : 15:05:21
|
quote:
jdeoneworld stored date as julian date in sql how i can converted in sql in normal date mm/dd/yy
I don't know what jdeoneworld is, but if you're asking about the storage of a date in a SQL Server database, the date is actually stored as a number of days before/after 1/1/1900. The default display of the date is YYYY-MM-DD HH:MM:SS. You can't change how the date is stored, but if you want to change the DISPLAY of the date, use the CAST or CONVERT functions. Both are outlined in Books Online.-------------------It's a SQL thing... |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-10 : 15:48:11
|
| I thought he was saying that he's got dates stored as integer (or float?) and wants to convert them into dates. It depends on when the origin is: in true Julian Dates, 0.0 is noon UTC, 1 Jan 4713 BCE, so you could use something like DATEADD(d, @jd - 2452254, '2001-12-10'), though that is half a day wrong if your dates are really datetimes.(thank you Emacs for today's Julian Day number!)Edited by - Arnold Fribble on 12/10/2001 15:52:21 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-10 : 16:06:45
|
| I don't know specifically about JDEdwards, but I know that a lot of accounting applications that claim they are using Julian dates, are actually using a funky char field in the form YYDDD or CYYDDD. So you can usually do something like: select CONVERT(datetime, SUBSTRING(datecol, 1, 2)+'0101', 12)+ (CAST(SUBSTRING (datecol, 3, 3) as int)-1)orselect CONVERT(datetime, CASE SUBSTRING(datecol, 1, 1) WHEN '0' THEN '19' ELSE '20' END + SUBSTRING(datecol, 2, 2)+'0101', 112)+ (CAST(SUBSTRING (datecol, 4, 3) as int)-1)Edited by - izaltsman on 12/10/2001 16:11:26 |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-10 : 16:14:03
|
| Given this page http://www.jdeplus.com/qdates.htm , looks like DATEADD(d, @jd - 103244, '2003-09-01')So why an origin in 1720?Edited by - Arnold Fribble on 12/10/2001 16:21:29 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-10 : 16:30:04
|
quote:
So why an origin in 1720?
It isn't... It's one of those "pseudo-Julian" dates I was talking about. Their "Julian" date of 103244 breaks down like this: 1 - means date is in year 2000+03 - the actual year244 - day of year |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-10 : 16:44:11
|
 Doh! Yes, it's quite obvious you're right from the bottom-right of the screenshot.Edited by - Arnold Fribble on 12/10/2001 16:48:18 |
|
|
|
|
|
|
|
|
date convert