Pivot columns to get 0 /1 flag columns

Question

Pivot columns to get 0 /1 flag columns

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

masterelaichi SSC-Addicted Points: 444 More actions · Answer 1

Jeff Moden wrote:

Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option - don't do this. Change the insert statement to use YYYYMMDD or YYYY-MM-DD (for date, datetime2 or datetimeoffset only) and then you won't have to worry about the date format setting.
INSERT INTO #input (employee_code,paydate,Paytype )
VALUES
    ('Emp_4', '20130723', 'Holiday_pay'),
    ('Emp_4', '20130723', 'Holiday_pay');
It might not be an option depending on where they're getting the data from.

that's correct. I can change the input tables in this example but not in the data that I work with as the source is external

I ran SET DATEFORMAT dmy before the INSERT statement and it worked

After running the set dateformat statement, I had a look at sp_helplanguage @@Language and it still says the date format is mdy. I expected it to display dmy. Is this right ?

This reply was modified 2 years, 2 months ago by masterelaichi.
This reply was modified 2 years, 2 months ago by masterelaichi.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

masterelaichi wrote:

Jeff Moden wrote:
Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option - don't do this. Change the insert statement to use YYYYMMDD or YYYY-MM-DD (for date, datetime2 or datetimeoffset only) and then you won't have to worry about the date format setting.
INSERT INTO #input (employee_code,paydate,Paytype )
VALUES
    ('Emp_4', '20130723', 'Holiday_pay'),
    ('Emp_4', '20130723', 'Holiday_pay');
It might not be an option depending on where they're getting the data from.
that's correct. I can change the input tables in this example but not in the data that I work with as the source is external
I ran SET DATEFORMAT dmy before the INSERT statement and it worked
After running the set dateformat statement, I had a look at sp_helplanguage @@Language and it still says the date format is mdy. I expected it to display dmy. Is this right ?

SET DATEFORMAT is a temporary setting just for the session. The reason why you're still seeing the dmy using the sp_helplanguage @@Language thing is (and it's a good thing), the setting doesn't change the language nor the format anywhere else. Just for the current session.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

To get the nice-warm-fuzzies on that, though, start a new session and run the following code... It should run just fine in the new session where you haven't set the date format...

DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

masterelaichi SSC-Addicted Points: 444 More actions · Answer 4

masterelaichi

SSC-Addicted

Points: 444

September 12, 2022 at 7:36 am

#4086512

thanks Jeff!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

It would be much better for you to go back to the sender and have them send that data in an unambiguous format. I get that some senders refuse - but the question should at least be asked and the reasons given for the request.

With that said, I still wouldn't use DATEFORMAT. I would use a staging table and insert the data as a string to that table - setting the size to VARCHAR(10) (or CHAR if it is always 10 characters).

Then perform an insert from the staging table where you could then use TRY_CONVERT and the specific format to convert that string to an actual date.

If you rely on DATEFORMAT - and you are expecting DMY but you actually get a mix of DMY and MDY the insert will fail. If you use TRY_CONVERT however, the insert will be successful and those values that cannot be converted will be NULL. You can then tie back to the staging table to figure out what was sent and why it failed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

masterelaichi SSC-Addicted Points: 444 More actions · Answer 6

Jeffrey Williams wrote:

It would be much better for you to go back to the sender and have them send that data in an unambiguous format. I get that some senders refuse - but the question should at least be asked and the reasons given for the request.
With that said, I still wouldn't use DATEFORMAT. I would use a staging table and insert the data as a string to that table - setting the size to VARCHAR(10) (or CHAR if it is always 10 characters).
Then perform an insert from the staging table where you could then use TRY_CONVERT and the specific format to convert that string to an actual date.
If you rely on DATEFORMAT - and you are expecting DMY but you actually get a mix of DMY and MDY the insert will fail. If you use TRY_CONVERT however, the insert will be successful and those values that cannot be converted will be NULL. You can then tie back to the staging table to figure out what was sent and why it failed.

That is what I do - we get the dataset in excel (through a secure file sharing system) which I then upload to the DB using SSIS after converting it into a CSV

This staging table is then converted into a format that can be consumed for my data modelling purposes

As a person relatively new to SQL, I come across numerous such problems so it is always good to know what causes them and how to go about tackling them. Since this query I posted came about using a dummy example it is still good to know how to solve such an issue using the language help and set dateformat statements

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Jeffrey Williams wrote:

...but you actually get a mix of DMY and MDY the insert will fail. If you use TRY_CONVERT however, the insert will be successful and those values that cannot be converted will be NULL. You can then tie back to the staging table to figure out what was sent and why it failed.

Prove that all the ones that worked where actually in the correct format. For example, 12/1/2022 will work correctly with either format and, obviously the two different formats will make the value have wildly different values.

If you come across such a thing in any data load, the data load is bad and must be scrapped in it's entirely because you cannot know which format should be used for the stuff that works with either format.

In other words, never use try convert on such a thing because you actually want the failure to inform you that there's something wrong with the data and you cannot tell all of it and so all the data must be assumed as bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16155 More actions · Answer 8

if you get the files in Excel and convert to CSV then it would be better if you define the format of the dates at that point - assuming the date is indeed a date in Excel and not just a string. format YYYYMMDD would always work in SQL Server.