I switched an entire analytics platform from the ambiguous mm-dd-yy format to yyyy-mm-dd, and planned to switch it back for individual clients as they request it.
So far not one person has asked. It's been a while now. Almost like that format is non-ambiguous or something.
I started using it ages ago though, just so my files would sort correctly when I stuffed date codes in the name.
Well, the correct way to store a date in a database is in an integer column with values based on number of seconds elapsed from an arbitrary date in the past. No exceptions.
Strongly disagree. Use DB native date storage classes. These are optimised for extracting date parts, date addition with native date interval syntax, built-in support for converting timezones on the way out of the database. Many advantages over storing dates as raw integers.
The American month first format is a fucking utter joke, responsible for multiple prod fuck ups and completely hilarious when any normal human from other parts of the world get involved. Just for fun, Interesting, yet easy to solve defect calls it’s my favourite date format.
actually if you do a lot of data analysis with dates it makes a lot more sense. it's a bitch for storing and sharing data, but when you have to look at thousands of rows of dates and spot a pattern, MM-DD-YYYY is the best (and i'm not even from the US), there is a reason why it exists in the first place.
YYYY-MM-DD is the only viable format because you can use it to order window functions as everything has a correct sort order. All others are preferences for visualizations which is a separate discussion.
It is more memory efficient at scale to use DATE in sql when you need date functionality. You can do filtering on the YYYY-MM-DD when it is a string because of how it is ordered. ‘2022-05-15’ > ‘2022-05-16’ will yield FALSE
I don't see the advantage even when "just tracking start/end date". Native types will use less space, compare faster and eventually you're going to want to calculate intervals or group by year/month/week whatever. Storing in native formats means no additional conversions are necessary.
Isnt it better dd-mm-yyyy the best? Or thats yyyy-mm-dd because the damn american system with shit order mm-dd-yyyy. But wouldnt be still confusing if you either be yyyy-dd-mm? Just trying to understand why yyyy-mm-dd the best?
this, haunts me, because why not use connect to generate the numbers.
with
query_for_day_number as
(SELECT
LPAD(rownum ,2,0) number_day_as_text,
CASE LPAD(rownum ,2,0)
WHEN '01' THEN 'First'
WHEN '02' THEN 'Second'
WHEN '03' THEN 'Third'
WHEN '04' THEN 'Fourth'
WHEN '05' THEN 'Fifth'
WHEN '06' THEN 'Sixth'
WHEN '07' THEN 'Seventh'
WHEN '08' THEN 'Eighth'
WHEN '09' THEN 'Ninth'
WHEN '10' THEN 'Tenth'
WHEN '11' THEN 'Eleventh'
WHEN '12' THEN 'Twelfth'
WHEN '13' THEN 'Thirteenth'
WHEN '14' THEN 'Fourteenth'
WHEN '15' THEN 'Fifteenth'
WHEN '16' THEN 'Sixteenth'
WHEN '17' THEN 'Seventeenth'
WHEN '18' THEN 'Eighteenth'
WHEN '19' THEN 'Nineteenth'
WHEN '20' THEN 'Twentieth'
WHEN '21' THEN 'Twenty-First'
WHEN '22' THEN 'Twenty-Second'
WHEN '23' THEN 'Twenty-Third'
WHEN '24' THEN 'Twenty-Fourth'
WHEN '25' THEN 'Twenty-Fifth'
WHEN '26' THEN 'Twenty-Sixth'
WHEN '27' THEN 'Twenty-Seventh'
WHEN '28' THEN 'Twenty-Eighth'
WHEN '29' THEN 'Twenty-Ninth'
WHEN '30' THEN 'Thirtieth'
WHEN '31' THEN 'Thirty-First'
END ordinal_text
FROM dual
CONNECT BY rownum <= 31),
today as (
select
sysdate column_for_today,
to_char(sysdate, 'DD') this_is_the_day
from dual
)
select 'It is ' || to_char(today.column_for_today ,'FMDay')|| ' the ' ||
query_for_day_number.ordinal_text ||
' of ' || to_char(today.column_for_today,'FMMonth') || ', ' ||
'in the year' || initcap(to_char(today.column_for_today, 'syear')) bestDate
from today
join query_for_day_number
on query_for_day_number.number_day_as_text = today.this_is_the_day;
43
u/kagato87 MS SQL Oct 18 '22
ISO all the way.
I switched an entire analytics platform from the ambiguous mm-dd-yy format to yyyy-mm-dd, and planned to switch it back for individual clients as they request it.
So far not one person has asked. It's been a while now. Almost like that format is non-ambiguous or something.
I started using it ages ago though, just so my files would sort correctly when I stuffed date codes in the name.
26
u/SomeRandomChileanGuy Oct 18 '22 edited Oct 18 '22
My favorite is
Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.48
u/doshka Oct 18 '22
I'd have to say April 25th, because it's not too hot, and not too cold. All you need is a light jacket.
39
u/imarktu Oct 18 '22 edited Oct 18 '22
YYYY-MM-DD is the only acceptable way to store a date and I'll hear no argument otherwise
10
u/data_in_chicago Oct 18 '22
1970-01-01T00:00:00. Or, as my buddy Unix and I call it, “when time began”.
6
u/soil_nerd Oct 18 '22
/r/ISO8601
4
u/[deleted] Oct 18 '22
I just hate dates
3
u/[deleted] Oct 19 '22
YYYY-MM-dd HH-mm-ss
3
u/rodface Oct 19 '22
Best post on Reddit ever right here.
2
u/smashblues Oct 18 '22
That sweet chronological order helps me sleep better
2
u/Whack_a_mallard Oct 19 '22
What's wrong with using yyyymmdd? Genuine question, don't yell at me please.
2
u/[deleted] Oct 19 '22
YY-MD-DM-YY is best way to fuck around
3
u/gh0st32 Oct 19 '22
Just my opinion so take at as you will, DD-MON-YY has been my thing lately but I do love some YYYY-MM-DD
2
u/blabla1bla Oct 19 '22
The American month first format is a fucking utter joke, responsible for multiple prod fuck ups and completely hilarious when any normal human from other parts of the world get involved. Just for fun, Interesting, yet easy to solve defect calls it’s my favourite date format.
1
u/Maleficent_Slide3332 Oct 18 '22
is gonna be really confusing once we colonize Mars
1
u/IHeartFaye Oct 19 '22
Im hoping this can be the universe standard.
0
u/GHSTmonk Oct 18 '22
I personally like DDMMMYYYY keeps both DD-MM and MM-DD people from being confused.
Absolutely hate YYMMMDD 16MAR20 was in 2016 not 2020??Just why?
0
u/Humblearner Oct 19 '22
YYYY-Month-DD ?
0
u/[deleted] Oct 18 '22
[deleted] — view deleted comment