September 13, 2018 at 1:14 pm
Hello
I am new to all this SQL. But I have been tasked with creating a SQL Database in SQL server 2017 that will allow labels to be created for Military shipments. and they use a YDDD format,
I have read almost all the posts here and other places to find out how to do this and I am left with so many more questions than I started with.
I had this all figured out in MS access and then I had to import it into SQL server 2017 and all the expressions that worked so well in access are now gone, they don't show in the formula spaces.
So what I am looking for is a formula that will return the current system date in YDDD format for these labels.
I hope some one can help me., because I am lost. I see all these very nice scripts and posts on how to write this properly but I cannot even find the area that I am allowed to put the script into. I know I am a novice at this but they say that admitting not knowing something is the first step to learning how to do it. So I'm asking the experts.
How do I put this formula in to return a result?
Thank you for your patience with my ignorance.
Dano
September 13, 2018 at 1:59 pm
This will give you the current date in the format YYYYDDDSELECT YEAR(GETDATE())*1000 + DATEPART(dayofyear,GETDATE());
September 13, 2018 at 2:10 pm
You could also create a re-usable function
CREATE FUNCTION dbo.GetJulianDate(
@SomeDate datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT JDate = YEAR(@SomeDate)*1000 + DATEPART(dayofyear,@SomeDate);
GO
And use it like this---------------------------------
-- USAGE: Current date
SELECT JDate FROM dbo.GetJulianDate(GETDATE());
---------------------------------
-- USAGE: Date variable
DECLARE @Mydate datetime = '2018-07-22';
SELECT JDate FROM dbo.GetJulianDate(@Mydate);
---------------------------------
-- USAGE: Date column from table
DECLARE @MydateTable table (
Somedate datetime
);
INSERT INTO @MydateTable (Somedate)
VALUES ('2018-05-01'), ('2018-06-19'), ('2018-07-23');
SELECT t.SomeDate, j.JDate
FROM @MydateTable AS t
CROSS APPLY dbo.GetJulianDate(t.SomeDate) AS j;
September 13, 2018 at 4:31 pm
To maintain my image as a pedantic poster, the yyyy-ddd display format Is called the ordinal date in ISO standards. The Julian date is used by astronomers and is a pretty big number, based on the rotations of the earth, starting at some astronomical event; you can Google it.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 14, 2018 at 8:34 am
to DesNorton
I will give these a try, thank you for responding to my issue and providing an answer that I could use.
to Joe Celko ...Yes I do realize that a true Julian date is a very long number ...but the US Military is rather particular about what they want, and they usually get it, if you want to do business with them.
Thanks so much
Dano
September 15, 2018 at 11:02 am
jcelko212 32090 - Thursday, September 13, 2018 4:31 PMTo maintain my image as a pedantic poster, the yyyy-ddd display format Is called the ordinal date in ISO standards. The Julian date is used by astronomers and is a pretty big number, based on the rotations of the earth, starting at some astronomical event; you can Google it.
Regardless, the military refers to it as a "Julian Date".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2018 at 11:40 am
Jeff Moden - Saturday, September 15, 2018 11:02 AMjcelko212 32090 - Thursday, September 13, 2018 4:31 PMTo maintain my image as a pedantic poster, the yyyy-ddd display format Is called the ordinal date in ISO standards. The Julian date is used by astronomers and is a pretty big number, based on the rotations of the earth, starting at some astronomical event; you can Google it.It's been a few decades since I did DOD contracting, but I seem to remember that we had "Julianized" dates instead of "ordinal dates" or just plain "Julian" instead. I think we were switching over to "ordinal" for dates within the year. I remember having to force myself to edit "daylight savings time" to take the S off and give me "daylight saving time" or to finally just chickened out to DST. Hey, I warned you I had my pedantic hat on 🙂
Regardless, the military refers to it as a "Julian Date".
Please post DDL and follow ANSI/ISO standards when asking for help.
September 16, 2018 at 9:29 am
jcelko212 32090 - Saturday, September 15, 2018 11:40 AMJeff Moden - Saturday, September 15, 2018 11:02 AMjcelko212 32090 - Thursday, September 13, 2018 4:31 PMTo maintain my image as a pedantic poster, the yyyy-ddd display format Is called the ordinal date in ISO standards. The Julian date is used by astronomers and is a pretty big number, based on the rotations of the earth, starting at some astronomical event; you can Google it.It's been a few decades since I did DOD contracting, but I seem to remember that we had "Julianized" dates instead of "ordinal dates" or just plain "Julian" instead. I think we were switching over to "ordinal" for dates within the year. I remember having to force myself to edit "daylight savings time" to take the S off and give me "daylight saving time" or to finally just chickened out to DST. Hey, I warned you I had my pedantic hat on 🙂
Regardless, the military refers to it as a "Julian Date".
Heh... not many know that the official term doesn't have the "s" you speak of in it. Pedantic is good but if someone leaves the "s" in during oral or written conversation, I'll still know what they mean. 😀
Agreed on the DOD thing... there was a change afoot decades ago but don't know what became of it for the same reason as you. They used to call the YDDD format "Julian Dates" (correct of not) and forced us to use the bloody things on 2-Kilo and 2-Lima forms. I always wondered how they overcame the "10 year wrap" and "Days between dates" issues with that format.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2018 at 1:45 pm
jcelko212 32090 - Thursday, September 13, 2018 4:31 PM
Agreed on the DOD thing... there was a change afoot decades ago but don't know what became of it for the same reason as you. They used to call the YDDD format "Julian Dates" (correct or not) and forced us to use the bloody things on 2-Kilo and 2-Lima forms. I always wondered how they overcame the "10 year wrap" and "Days between dates" issues with that format.
LOL! My favorite military insanity was when France was part of NATO. The dates used Roman numerals for the months! This was so the French would not be offended by anything that looked anything close to English
Please post DDL and follow ANSI/ISO standards when asking for help.
September 16, 2018 at 5:29 pm
Jeff Moden - Saturday, September 15, 2018 11:02 AMjcelko212 32090 - Thursday, September 13, 2018 4:31 PM
Agreed on the DOD thing... there was a change afoot decades ago but don't know what became of it for the same reason as you. They used to call the YDDD format "Julian Dates" (correct or not) and forced us to use the bloody things on 2-Kilo and 2-Lima forms. I always wondered how they overcame the "10 year wrap" and "Days between dates" issues with that format.
LOL! My favorite military insanity was when France was part of NATO. The dates used Roman numerals for the months! This was so the French would not be offended by anything that looked anything close to English
What a strange debate. Surely you both realise that the English language is not dictated by ISO, or by the USA's DOD, or by astronomers, or by any other minor clique, because it is determined by the concensus or all the world's native English speakers and hence common usage of speakers of English. Julian dates are dates expressed using the Julian calendar as defined about the beginning of 45 BC, and if ISO or DOD or Astronomers use the term "Julian date" for something else thay can do so in there own specific jargon and no-one objects. But if people working in computing want to communicate with each other without first saying, for example,"we are speaking USA DOD jargon, not English", they had better stick to the generally accepted English meaning.
There are various other things that are related to the Hukian calendar, for example "Julian day" (number of days since beginning of first epoch of the Julian calendar - that's what astronomers tend to mean) or something in <years>/<days> form where years is the Julian year number and days is the day number within the year plus a decimal fraction of a day indicating the time of day (so that <days> = 1.5500 indicates13:12 on 1st January) - I think that was what ISO called "ordinal dates" although it seems odd to call something accurate to less than a day a "date" rather than a "date and time", but none of these can override the generally understood meaning of "Julian date".
Tom
September 16, 2018 at 5:54 pm
jcelko212 32090 - Sunday, September 16, 2018 1:45 PMLOL! My favorite military insanity was when France was part of NATO. The dates used Roman numerals for the months! This was so the French would not be offended by anything that looked anything close to English
Actually, that's completely wrong. The version one hears over here in Britain (and in France, and in Germany, and so on) is as described below:-
The USA refused to use either YYYYMMDD or DDMMYYYY order because the USA loves the utterly illogical idea that days fit in between months and years, instead of months between days and years. The British said they would continue to use the standard used in English for dates throughout the English-speaking world outside of North America . The other NATO nations wanted to use the International Standard (which was,as you know, months in the middle) but the Americans wouldn't accept at. In order o avoid having no agreed format at all, the French then suggested that Roman numerals could be used for the month, because then we could always always distinguish months from days - and the USA's intransigent demand that an illogical ordering should be used would thereby be avoided. No-one has ever told me that any standard was adopted that determined whether days were first or last - but as at the time it would have been a little more than half a century until a current day of month number cold be confused with the last two digits a year number (because this stuff was agreed in 1949, which was a bit later than later than 1931 but still earlier than 1951) so I imagine no-one was worried about that.
Yes, lots of people (especially British civil servants) like pretending that the French are anti-English, but the Americans are said to have demonstrated on that occassion (as on many others) that they were more anti-English than the French.
Tom
September 16, 2018 at 10:08 pm
jcelko212 32090 - Saturday, September 15, 2018 11:40 AMJeff Moden - Saturday, September 15, 2018 11:02 AMjcelko212 32090 - Thursday, September 13, 2018 4:31 PMAgreed on the DOD thing... there was a change afoot decades ago but don't know what became of it for the same reason as you. They used to call the YDDD format "Julian Dates" (correct or not) and forced us to use the bloody things on 2-Kilo and 2-Lima forms. I always wondered how they overcame the "10 year wrap" and "Days between dates" issues with that format.
LOL! My favorite military insanity was when France was part of NATO. The dates used Roman numerals for the months! This was so the French would not be offended by anything that looked anything close to English
What a strange debate. Surely you both realise that the English language is not dictated by ISO, or by the USA's DOD, or by astronomers, or by any other minor clique, because it is determined by the concensus or all the world's native English speakers and hence common usage of speakers of English. Julian dates are dates expressed using the Julian calendar as defined about the beginning of 45 BC, and if ISO or DOD or Astronomers use the term "Julian date" for something else thay can do so in there own specific jargon and no-one objects. But if people working in computing want to communicate with each other without first saying, for example,"we are speaking USA DOD jargon, not English", they had better stick to the generally accepted English meaning.
There are various other things that are related to the Hukian calendar, for example "Julian day" (number of days since beginning of first epoch of the Julian calendar - that's what astronomers tend to mean) or something in <years>/<days> form where years is the Julian year number and days is the day number within the year plus a decimal fraction of a day indicating the time of day (so that <days> = 1.5500 indicates13:12 on 1st January) - I think that was what ISO called "ordinal dates" although it seems odd to call something accurate to less than a day a "date" rather than a "date and time", but none of these can override the generally understood meaning of "Julian date".
Yep, Totally agreed. It was all "jargon" based as so many things are in esoteric settings.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply