Convert Julian Date to Yyyy-Mm-Dd

Convert Julian Date to YYYY-MM-DD

You can select each part of the date using datepart()

SELECT DATEPART(yy, 95076), DATEPART(dy, 95076)

+++EDIT: I misunderstood something. Here's my correction: +++++

SELECT DATEADD(day, CAST(RIGHT('95076',3) AS int) – 1, CONVERT(datetime,LEFT('95076',2) + '0101', 112))

convert julian date to calendar date YYYY-MM-DD

You're looking for str_to_date('2020356','%Y%j') (gives 2020-12-21) , where %Y is the format specifier for a four-digit year, and %j is the day of the year.

For the longer date referenced in your question, just take the LEFT('20202800234036',7) and apply STR_TO_DATE()` to that:

str_to_date(LEFT('20202800234036',7),'%Y%j') -- 2020-10-06

There's a reference for these formats here

convert Julian day number to date time format yyyy-mm-dd hh:mm:ss in R

as.POSIXct('2000-01-01')+((JDN+0.5)*24*60*60)

Convert a JDE Julian Date (CYYDDD) to YYYY-MM-DD format in XSLT or SQL

Here are a couple of solutions for dealing with JDE Julian Dates (which differ from the official definition a Julian date). For JDE:

  • The units, tens & hundreds part of the number refers to days since the start of the year; with '001' corresponding to 1st January.
  • Everything in the thousands and above corresponds to the year; with 100 representing the year 2000.
  • Example: 100001 is 2000-01-01

Both solutions below work in the same way:

  • Date divided by 1000 & result floored (rounded down) gives the year part of the number (i.e. custs off the last 3 digits). e.g. 123321 => 123.
  • Adding 1900 to this gives us the actual year. e.g. 123 + 1900 => 2023
  • We then convert this to a valid date by building up a date string for 1st Jan and converting the string to a date; e.g. 2023 -> 2023-01-01.
  • Date modulus 1000 returns the days offset part of the figure (i.e. essentially cuts off all but the last 3 digits). e.g. 123321 => 321.
  • We take 1 from the days offset (i.e. because 1st Jan is 001; not 000). e.g. 321 => 320
  • We then offset our 1st Jan date calculated from the year part by the number of days from the day part. e.g. 2023-01-01 + 320 days => 2023-11-17

MS SQL

create function dbo.ConvertJulianToDate (
    @JulianDate integer
)
returns date 
begin
    return dateadd(
       day
       , @JulianDate % 1000 - 1
       ,cast(
           cast(
               @JulianDate / 1000 + 1900 
               as varchar(4)
           ) + '-01-01' 
           as date
       )
    ) 
end

Example Usage

select dbo.ConvertJulianToDate(117175)

XSLT 2.0

<xsl:template name="JulianToDate">
    <xsl:param name="JulianDate" select="./text()" />
    <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
    <xsl:variable name="days" select="($JulianDate mod 1000) - 1" />
    <xsl:value-of select="xs:date(concat($year,'-01-01')) + xs:dayTimeDuration(concat('P',$days,'D'))"/>
</xsl:template>

Full Code

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform 
    version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:fn="http://www.w3.org/2005/xpath-functions"
    exclude-result-prefixes="xsl xs fn"
>
<xsl:output method="xml" encoding="UTF-8" indent="yes" />

    <xsl:template match="/*">
        <Root>
            <CallOnCurrentElement>
                <xsl:call-template name="JulianToDate" />
            </CallOnCurrentElement>
            <PassValue>
                <xsl:call-template name="JulianToDate">
                    <xsl:with-param name="JulianDate">114026</xsl:with-param>
                </xsl:call-template>
            </PassValue>
        </Root>
    </xsl:template>

    <xsl:template name="JulianToDate">
        <xsl:param name="JulianDate" select="./text()" />
        <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
        <xsl:variable name="days" select="($JulianDate mod 1000) - 1" />
        <xsl:value-of select="xs:date(concat($year,'-01-01')) + xs:dayTimeDuration(concat('P',$days,'D'))"/>
    </xsl:template>
</xsl:transform>

Sample XML

<?xml version="1.0" encoding="UTF-8"?>
<julianDate>117175</julianDate>

Fiddle Demo: http://xsltransform.net/6pS1zCU

XSLT 1.0

Date functions aren't available in XSLT 1.0, so it's very hacky. However I believe this should work.

    <xsl:template name="JulianToDate">
        <xsl:param name="JulianDate" select="./text()" />
        <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
        <xsl:variable name="days" select="($JulianDate mod 1000)" />
        <xsl:variable name="isLeapYear" select="(($year mod 4) = 0) and ((($year mod 100) != 0) or (($year mod 400) = 0))" />
        <xsl:variable name="month" select="1" />
        <xsl:variable name="day" select="1" />
        <xsl:variable name="febDays">
            <xsl:choose>
                <xsl:when test="$isLeapYear">29</xsl:when>
                <xsl:otherwise>28</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:choose>
            <xsl:when test="31 >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days" />
                    <xsl:with-param name="MM" select="1" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(31 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days -31" />
                    <xsl:with-param name="MM" select="2" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(63 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (31 + $febDays)" />
                    <xsl:with-param name="MM" select="3" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(92 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (63 + $febDays)" />
                    <xsl:with-param name="MM" select="4" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(123 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (92 + $febDays)" />
                    <xsl:with-param name="MM" select="5" />
                    <xsl:with-param name="yyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(153 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (123 + $febDays)" />
                    <xsl:with-param name="MM" select="6" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(184 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (153 + $febDays)" />
                    <xsl:with-param name="MM" select="7" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(215 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (184 + $febDays)" />
                    <xsl:with-param name="MM" select="8" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(245 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (215 + $febDays)" />
                    <xsl:with-param name="MM" select="9" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(276 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (245 + $febDays)" />
                    <xsl:with-param name="MM" select="10" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(306 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (276 + $febDays)" />
                    <xsl:with-param name="MM" select="11" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(337 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (306 + $febDays)" />
                    <xsl:with-param name="MM" select="12" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:message terminate="yes">Invalid Julian Date</xsl:message>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>
    <xsl:template name="CreateDate">
        <xsl:param name="yyyy" />
        <xsl:param name="MM" />
        <xsl:param name="dd" />
        <xsl:value-of select="concat(substring(10000 + $yyyy, 2),'-',substring(100 + $MM, 2),'-',substring(100 + $dd, 2))"/>
    </xsl:template>

(This just replaces the equivalent template from the XSLT 2.0 version; all else (version number aside) should be identical.

Convert Julian Date CYYDDD to DD/MM/YYYY

I found a sample code from this site, to convert the Julian Date to DD/MM/YYYY format

DECLARE @Sample AS VARCHAR(6) = '115351'
SELECT CONVERT(VARCHAR(10), DATEADD(DAY, CONVERT(INT, @Sample) - ((1000*(CONVERT(INT, @Sample)/1000)))-1, DATEADD(YEAR, CONVERT(INT, @Sample/1000), '1 Jan 1900')), 103) AS Result

it will convert the 351 th day of the year 2015, so the result will be 17/12/2015

Demo on db<>fiddle

UPDATE:

As cars10m suggested in the comment, using % Modulus operator, the query above can be reduced as

DECLARE @Sample AS VARCHAR(6) = '115351'
SELECT CONVERT(VARCHAR(10), DATEADD(DAY, CONVERT(INT, @Sample) % 1000 -1, DATEADD(YEAR, CONVERT(INT, @Sample/1000), '1 Jan 1900')), 103) AS Result

Convert julian date to YYYYMMDD using python datetime with regex

Regex is the wrong tool for the job when it comes to converting dates, you can still use it to extract the 'interesting' part of your string (YYYY_DDD from the image name), tho:

import re

filename = 'abc_8g_1980_312.tif'
date_str = re.search(r'\d{4}_\d+', filename).group()  # '1980_312'

Of course, if there are more patterns that might occur, I'd advise making the pattern more strict, for example you can put the above regex in a group and then match \.tif$ after it to ensure it always searches for the pattern at the end of tif filenames. If it's always this format, you can completely forgo regex and just split / partition your string to extract the 'important' part but I'll leave that as an exercise for you ;)

Once you get the YYYY_DDD part from the filename, you can use datetime.datetime.strptime() to get to the actual date, e.g.:

import datetime

real_date = datetime.datetime.strptime(date_str, '%Y_%j')
# datetime.datetime(1980, 11, 7, 0, 0)

Finally, to get to your desired output you can use datetime.datetime.strftime():

str_date = real_date.strftime('%Y%m%d')
# 19801107

Trying to convert a 7 digit julian date into MMDDYY format

The simplest would seem to be to take the left as the year, and the add the days (-1) to make a date. Also, rather than using a format of MMDDYY I'm going to go straight a date datatype. If you want it in a specific format, that's for your presentation layer.

SELECT JulianDate,
       CONVERT(date,DATEADD(DAY,RIGHT(JulianDate,3)-1,CONVERT(datetime,LEFT(JulianDate,4)))) AS ActualDate --4 int strings are iterpreted as the year, so I'm going to take advantage of that
FROM (VALUES('2005020'))V(JulianDate);

Based on the comments on the answer, it appears that the OP has some dates that don't conform to the format that stated (yyyyddd). Therefore what we could use here is a calendar table, here, and then LEFT JOIN to it and see what bad rows you get (and INNER JOIN to get the dates).

You can create the table with something like this:

CREATE TABLE dbo.CalendarTable (CalendarDate date NOT NULL PRIMARY KEY,
                                CalenderYear AS DATEPART(YEAR, CalendarDate) PERSISTED,
                                CalenderMonth AS DATEPART(MONTH, CalendarDate) PERSISTED,
                                CalenderDay AS DATEPART(DAY, CalendarDate) PERSISTED,
                                CalenderMonthName AS DATENAME(MONTH, CalendarDate),
                                JulianDate AS DATEPART(YEAR,CalendarDate) * 1000 + DATEDIFF(DAY,DATEFROMPARTS(DATEPART(YEAR, CalendarDate),1,1),CalendarDate) + 1 PERSISTED); --Some example columns



WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3, N N4, N N5, N N6),
Dates AS(
    SELECT CONVERT(date,DATEADD(DAY, T.I, '19000101')) AS CalendarDate
    FROM Tally T)
INSERT INTO dbo.CalendarTable(CalendarDate)
SELECT CalendarDate
FROM Dates
WHERE CalendarDate < '21000101';
GO

Then we can do something like this to get the bad rows:

SELECT YT.JulianDate
FROM dbo.YourTable YT
     LEFT JOIN dbo.CalendarTable CT ON YT.JulianDate = CT.JulianDate
WHERE CT.JulianDate IS NULL;

Related Topics

How to Escape Back Slash in SQL Server

How to Tell If a Value Is Not Numeric in Oracle

Select Query to Remove Non-Numeric Characters

Why Postgresql Does Not Like Uppercase Table Names

Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself

Find Out Count of Employees Joined in January Month

Sql Query Is Taking More Than 4 Minutes

How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database

Mysql Left Join With Limit 1 Not Returning Desired Result

Selecting Only One Duplicate Record

Sql Server Count Instances of Most Frequent Data

Sql Group by Age Range

Extract String Part Between Single Quotes from Formula'S Divisor

Sql Query to Check If a Name Begins and Ends With a Vowel

How to Open a Huge .Sql File

Getting Student Name With Highest Total Mark in SQL

Column Is of Type Timestamp Without Time Zone But Expression Is of Type Character

How to Use Return Value of Insert...Returning in Another Insert


Leave a reply



Submit