The Heap™ – Consultancy ©®

swasheck

2:03 AM

@bluefeet Jekyll??? really??? c'monman

Andriy M

5:11 AM

@RoyiNamir Why would it fail? The date part is YYYYMMDD, which is unambiguous for conversion. Should be fine.

Royi Namir

6:09 AM

@AndriyM If it was only a date , then yyyymmdd would be fine

But since there's also a time , then I believe ( according to the article) that there could be a problem

It is either only date or , date with time as ISO :

YYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]

(Correct me if I'm wrong)

Andriy M

He is talking about the older types, datetime and smalldatetime. They consist of both the date and the time. So it just wouldn't make sense if YYYYMMDD was unambiguous for those types but YYYYMMDD hh:mm:ss[.nnn] was not.

Anyway, it is easy to verify using Aaron's method (SET LANGUAGE), which I've just done :)

The reason he didn't include the YYYYMMDD format with the time part was probably because he was talking about dates specifically. YYYYMMDD is unambiguous regardless of whether you are specifying a time along with it or not. In contrast, YYYY-MM-DD is unambiguous only as part of the YYYY-MM-DDThh:mm:ss[.nnn] format

Royi Namir

@AndriyM So , just to check , are you telling me that YYYYMMDD HH:MM:SS (notice no T) - will always be fine ?

Andriy M

@RoyiNamir Yes, that is what I'm telling you

Royi Namir

So the only problem is with the seperators ?

Andriy M

You shouldn't take my word for it, though, even if I were Aaron. Just try for yourself:

SET LANGUAGE FRENCH;
GO
SELECT CONVERT(DATETIME, '2009-10-13');
GO
SELECT CONVERT(DATETIME, '20091013');
GO
SELECT CONVERT(DATETIME, '20091013 11:12:14');
GO
SET LANGUAGE ENGLISH;

Royi Namir

6:24 AM

This should definitly be mentioned in the article

Thank you for correcting me and clarifying

Should I revert my edit here ? stackoverflow.com/a/19737882/859154

Andriy M

Well, the edit seemed unnecessary. But it may have made things better by replacing two conversions with one, and there's no sign that the rest of the code particularly relies on the previous format, so... I'd probably leave it to avoid making changes to the thread (and thus bumping the question to the top of the queue) again. But there may be other opinions.

Royi Namir

I don't get it. the trigger works only for update( and supposed to , and also it's working fine). Heere he checks what kind of operation is this :

IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = 'U'
       ELSE
               SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'

So U is the update one

But later the insert query only execute for I,D :

What am I missing ?

Isn't he after @type in ('U') ?

Gemtastic

6:42 AM

Great, I'm back to zero when it comes to the role permissions in Postgres... Do I have to make it a User to give it access to the db it's the owner of?

(I granted the role login)

Learner

what if I have 2 para and I want to delete rows based on either both if provided or only using one whichever is provided ?

Tom V

huh?

Learner

its a stored procedure

with optional parameters

Andriy M

@Learner You may be talking about dynamic search conditions.

Tom V

oh, it's a bit early in the morning to decrypt questions :)

Andriy M

6:54 AM

@RoyiNamir Not sure but perhaps OR @Type = 'D' would suffice, because in an AFTER INSERT trigger, the result of COLUMNS_UPDATED() would have all bits set to 1.

(And thus the first condition, SUBSTRING..., would work for an insert.)

Learner

I was looking for something like this

8

Q: Passing sets and optional parameters to a stored procedure T-SQL - ASP.NET

A big task for me. I have a few questions. You guys already helped me so much, but I get one difficulty after another and progressing unbelievably slowly. These questions will better describe my tasks. I have an aspx page with the following controls: TextBox, CheckBoxList, DropDownList. They are...

thank you

Andriy M

7:32 AM

@RoyiNamir To clarify what I said, the IF is supposed to verify that there are things to log in the audit table.

The SUBSTRING predicate checks if the column in the current iteration of the loop is affected by the trigger operation. If it is not affected, the server goes on to presume that the operation may be either an INSERT or a DELETE and checks whether it is so (using the IN predicate). But, as I said before, the inserts should be caught by SUBSTRING as well, so the other condition could be just @Type = 'D'.

But D is delete

Yes

?

The task is to audit the current change. It can be any of the three operations. If it's an update, the trigger checks each column whether it is updated, so that it doesn't log a column that isn't updated.

If it's a delete, the SUBSTRING condition will return false, but the change should still be logged. That's what the OR @Type... is for.

Royi Namir

Oh, that was my missing part

Thank you.

I need to learn this stuff (columns_update()) plus the ` mod 8` thing regarding number of columns

(what difference does it make 8 columns or more anyway....)