Julian time conversion problem

Question

Post reply

Julian time conversion problem

Jon V

Mr or Mrs. 500

Points: 533
More actions
February 28, 2011 at 4:27 pm

#247451

Hi all--I have a problem where I am trying to concatenate two fields:
- a date time field called [startdate] with a "mm/dd/yyyy 00:00:00" (with the "00:00:00" hard-coded into the field);
- an integer field called [starttime] with a Julian format set to it.
Let's say that I have a [startdate] of "02/28/2011 00:00:00" and a [starttime] of 73. How would I concatenate the converted Julian time format to the normal date? Would I need to convert the startdate field to Julian format, concatenate them, and then convert them back to Gregorian calendar?
Thanks in advance,
Jonathan

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Jonathan,

You're not using Julian dates/times in a format that I'm familiar with. What do you mean by a starttime of Julian 73?

(FYI: most people butcher up the real Julian format into something that want to use...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jon V Mr or Mrs. 500 Points: 533 More actions · Answer 2

Hi Wayne--The Julian format I'm talking about is the typical format for the Julian calendar. The number I used in the example translates to "1900-03-14 00:00:00" when I plug it into Excel. Does this help?

- Jonathan

Jon V Mr or Mrs. 500 Points: 533 More actions · Answer 3

PS: What I'm trying to do is match up a start date in normal datetime format of mm/DD/yyyy and match it up with the time portion of the "starttime" field. I'm supporting an application with these two items split into two separate fields within the back end table...why it's set up this way, not sure but I'm trying to combine it into something workable.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Jon V (2/28/2011)
Hi Wayne--The Julian format I'm talking about is the typical format for the Julian calendar. The number I used in the example translates to "1900-03-14 00:00:00" when I plug it into Excel. Does this help?
- Jonathan

If that's the date you actually want for that "Julian" value, then this will accomplish the task...

SELECT CAST(73 - 1 AS DATETIME)

You need the minus 1 because SQL Server treats 1900-01-01 as day zero.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon V Mr or Mrs. 500 Points: 533 More actions · Answer 5

Here's an example of data that I'm trying to make sense of:

startdate datetime

starttime int

startdate starttime

-------------------------------------

2011-02-10 00:00:00 73

2011-02-10 00:00:00 25434

2011-02-10 00:00:00 25567

2011-02-10 00:00:00 35265

....

Again, startdate is in typical Gregorian date/time format, but the table I'm working with has the *time* field set in Julian date/time format. I'm not sure why this was done, I'm just interpreting it from the original application design.

The startdate does not need to change; the date in there is correct and useful. What I'm trying to interpret and/or convert is a meaningful *time* from Julian to Gregorian format, so that my startdate + starttime looks like: 2011-02-10 02:05:33 and eliminate/convert the Julian portion of this structure. Does this clarify?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Hi ,

The problem here is that we have no idea what value

25434 for example will represent.

Is that seconds ? milliseconds ? centi-seconds ? deca-seconds ?

2 hrs 54 minutes and 34 seconds ?

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

Jon V (2/28/2011)
Here's an example of data that I'm trying to make sense of:
startdate datetime
starttime int
startdate starttime
-------------------------------------
2011-02-10 00:00:00 73
2011-02-10 00:00:00 25434
2011-02-10 00:00:00 25567
2011-02-10 00:00:00 35265
....
Again, startdate is in typical Gregorian date/time format, but the table I'm working with has the *time* field set in Julian date/time format. I'm not sure why this was done, I'm just interpreting it from the original application design.
The startdate does not need to change; the date in there is correct and useful. What I'm trying to interpret and/or convert is a meaningful *time* from Julian to Gregorian format, so that my startdate + starttime looks like: 2011-02-10 02:05:33 and eliminate/convert the Julian portion of this structure. Does this clarify?

I agree with Dave... we don't know what you "Julian" information represents. However, if you check the Max for the column and it doesn't exceed 86,400 (the number of seconds in a day), I suspect the number represents time in the form of seconds. In that case, the following should work correctly...

SELECT DATEADD(ss,yourjuliancolumn,yourdatecolumn)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon V Mr or Mrs. 500 Points: 533 More actions · Answer 8

Hi Jeff--Your theory worked! Thanks to all who replied, it's appreciated.

In answer to your question where the Julian date/time came into play, the application back end has legacy extract bcp code which uses Julian date/time conversion to create some kind of an offset for parts of the extract process. It's a work in process.

Thanks again,

Jonathan