Can I convert Dates from yyyy/mm/dd to Julian in S

# Thread: Can I convert Dates from yyyy/mm/dd to Julian in S

1. Senior Member
Join Date
Dec 1969
Posts
406

## Can I convert Dates from yyyy/mm/dd to Julian in S

Hi,<BR>I&#039;m working with an ASP graphing application and I can&#039;t get the date axis to work properly. I think that in order to show my data properly, I need to convert the date into successive integers (perhaps julian-date). <BR><BR>I can&#039;t just write out the date as yyyymmdd, because that would leave big gaps between months (19990228 jumps to 19990301) or years (19991231 jumps to 20000101), which would mess up my graph axis. My data spans many years, so I can&#039;t simply write out a 3 digit julian date because I wouldn&#039;t be able to tell what year it was from.<BR><BR>Is there a sql function I can use to convert my dates into a successive series of integers? I figure if I can do this, I can store and display the integers, while labeling them with the actual date values from my db.<BR><BR>Any thoughts or suggestions?<BR>Thanks!<BR>Mike

2. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: Can I convert Dates from yyyy/mm/dd to Julian

look under DatePart() in books online.<BR><BR><BR><BR>j<BR>http://rtfm.atrax.co.uk/

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Trivial...DATEDIFF

Just subtract the starting date of the graph from each date along the axis.<BR><BR>SELECT theDate, DATEDIFF( &#039;d&#039;, &#039;1999-01-01&#039;, theDate ) AS countOfDays<BR>FROM sometable<BR>WHERE theDate &#062;= &#039;1999-01-01&#039;<BR>ORDER BY theDate<BR><BR>DATEDIFF works--with minor variations--in Access, SQL Server, and MySQL. You don&#039;t say what DB your are using, so that Access format is the best guess you get. <BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Hmmm...how does that help?

Okay...I&#039;m looking it up...<BR><BR>Nope, no surprises. <BR><BR>Okay, tell me how you&#039;d use it?<BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Actually, in Access...

...it&#039;s even easier:<BR><BR>Assuming the "theDate" field in the DB truly holds ONLY a date:<BR><BR>SELECT theDate, theDate-#1999-01-01# AS countOfDays<BR>FROM sometable<BR>WHERE theDate &#062;= #1999-01-01#<BR>ORDER BY theDate<BR><BR>If you subtract dates in Access (and in VBScript) you *do* get then number of days between them.<BR><BR>But DateDiff is much more universal.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
406

## RE: Actually, in Access...

Thank Bill and Atrax-<BR><BR>I am indeed using access, and i&#039;m working on trying the suggestion above. Thanks very much for you help. This is all so different from the PROLOG coding environment I&#039;m so used to... ;)<BR><BR>Mike

7. Senior Member
Join Date
Dec 1969
Posts
19,082

## maybe I've got the wrong end of the stick....

... but using DatePart should enable you to pull a date out into whatever [string based] format you need reasonably easily. or would it? now I think of it maybe you&#039;re right.

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Are you serious?

Naw, you can&#039;t be.<BR><BR>PROLOG died (a somewhat undeserved death, probably pushed over by TurboProlog) years ago.<BR><BR>I *liked* PROLOG. Even if I never could figure out anything truly useful to do with it.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
406

## RE: Are you serious?

I think PROLOG probably died before I was born (76)... I&#039;ve just heard you mention it as a somewhat archaic language in a previous response to one of my inane questions.<BR><BR>Thanks again!

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well...it would work...

...so long as you were working within a single year.<BR><BR> DATEPART( dy, theDate )<BR><BR>gives you the Julian day of the year. But if your timeline extends across multiple years, then Jan 10, 2002, and Jan 10, 2003, would each have the same "dy" value.<BR><BR>You could compensate for that:<BR> SELECT DATEPART( dy, theDate ) + INT( 365.25 * ( DATEPART( yyyy, theDate ) - 2000 ) )<BR> FROM table<BR> WHERE theDate &#062;= &#039;2000-1-1&#039;<BR><BR>Please don&#039;t ask me how to adjust it for other starting dates. My head hurts.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•