Fuck the UK/US date format difference in the arse with a shaved giant redwood!!!!!!!!

The BBQ Pit
1

I am pulling data off a database where the date is in this format - yy/mm/dd.

When I open up the data in excel a date of 1st of October 2008 becomes the 8th of October 2001.

If I open up the data format properties in regional settings in control panel, and then choose yyy-MM-dd and click OK I get

“One of the characters you have entered is invalid”

I HAVEN’T ENTERED ANY FUCKING CHARACTERS!!!
If I simply change the lot to US Excel won’t recognise this change until I reboot. So I’ll have to make the change, reboot, run the query, change it back, reboot again.
OR

fuck about with the query re-arranging the dd mm and yy using the substring function, and then delete the fuckwankbollocks columns within excel because I want to use the * (there are a lot of columns)
Why can’t excel just fucking let me tell it “THESE ARE AMERICAN DATES IN THE FORMAT YY/MM/DD SO LEAVE THEM THE FUCK ALONE FUCKROSOFT EXWANK”

“ACTUALLY - LEAVE THE REST OF THE DATA THE FUCK ALONE IF I WANT YOU TO LEAVE IT THE FUCK ALONG O-FUCKING-K???”
And before anyone says. I know yy/mm/dd isn’t the main us format (or is it) but it is A us format and when my computer is all in US mode then it works.

But I don’t want it in US mode because then my @ and " keys are in the wrong place.

2

Even better–why aren’t people using four-digit years even now? And why aren’t they using the only unambiguous date format, yyyy-mm-dd, as specified in ISO 8601?

3

I think that would hurt.

4

Don’t forget the US/Canada format difference. When I first applied for a marriage license, my birthday was input as the 5th of December instead of the 12th of May.

5

Further more, if I format the cells as text, then run the ‘text to colums’ procedure. Excel still fucks with the dates.

6

Because (as mentioned in OP) If I try to set it to that it complains that a character I [didn’t] enter is invalid.

7

And doesn’t tell you which one. Gaah. Typical MS error message. Reminds me of the old joke:

Two people are flying a helicopter through the fog, and they realize that they are lost. They cautiously descend and come out of the fog above an office park. The pilot spots someone below and calls out, “Where are we?”
The person on the ground replies, “You are in a helicopter.”
The helicopter pilot thanks the person on the ground, then turns to the passenger and says, “I know exactly where we are. We are above Microsoft’s headquarters in Redmond, Washington.”
The helicopter passenger asks, “How do you know that?”
“Because of the response to my question. It was perfectly accurate, yet utterly useless.”
:slight_smile:

And why aren’t the people giving you the data in the proper ISO-8601 format to start with?

8

Because they want to be awkward.

It’s data from an ageing system.

9

Well… Having calmed down a bit I decided to create the following function…

CREATE FUNCTION [dbo].[ddmmyy] (@value NVARCHAR(8))
RETURNS NVARCHAR(8)
AS
BEGIN
RETURN (substring(@value,7,2)+’/’+substring(@value,4,2)+’/’+substring(@value,1,2))
END

10

If it’s an Oracle date, it’s all just a huge number of time increments as far as the database is concerned. that you can format in anyway you want.

TO_CHAR(dateCol,‘YYYY-MM-DD’) etc.

or even get your whole session output changed.
“ALTER SESSION set nls_date_format = ‘YYYY-MM-DD’;”

If it’s a timestamp it works the same way.

And if they are storing dates as a string of character, then buy a gun and shoot them.

11

Yes they are stored as strings. Buy me a ticket to Oregon and I will.

Edit: Actually they start out life as strings in a pipe delimited text file, so the people I should shoot are the guys down the road who designed the database into which they are imported.