Hulde!
coentje
@coentje
Best posts made by coentje
Latest posts made by coentje
-
RE: Conversion FUN
@db2 said:
@coentje said:
@db2 said:
Don't worry, that's still a pretty awful way to do it. I usually just do this:
<font size="2" face="Lucida Console">DATEADD(dy, DATEDIFF(dy, 0, @date), 0)</font>
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
Still ugly indeed but no longer a WTF.
Of course, this is all assuming you aren't running 2008 and can't just do CAST(@date AS date).
Nope, this customer still on 2005
-
RE: Conversion FUN
@db2 said:
Don't worry, that's still a pretty awful way to do it. I usually just do this:
<font face="Lucida Console" size="2">DATEADD(dy, DATEDIFF(dy, 0, @date), 0)</font>
You can also cast it to an int and then back to a datetime, since datetime is actually stored as a float type internally.
Still ugly indeed but no longer a WTF.
-
RE: Conversion FUN
@lilakuh said:
This is not necessarily a WTF. The code could be used to compare only the date part of two dates, ignoring the time component, since there is no date-only datatype prior to SQL Server 2008.
I stand corrected. You are absolutely right, I was jumping to conclusions here based on previous experiences with this code. Should have thought a little longer before posting.
-
Conversion FUN
Just one of the little gems found in one of the DB's i'm maintaining
SELECT DISTINCT T1.Ordernumber FROM dbo.OrderEdi T1 WHERE CAST(CONVERT(VARCHAR, t1.ProcessingDate, 106) AS SMALLDATETIME) = CAST(CONVERT(VARCHAR, GETDATE(), 106) AS SMALLDATETIME) )
b.t.w. the t1.ProcessingDate is already a SMALLDATETIME field ...
-
Payed by LOC??
Someone asked me to find a problem that should be located in this view...
I snipped some (about 3000) lines
SELECT /* snip about 100 lines */ FROM Live2007.ORDERSET T1 INNER JOIN Live2007.SITE T2 ON T2.STAMP1 = T1.STAMP1 LEFT OUTER JOIN Live2007.SITE T4 ON T1.GROSID_1 = T4.ID LEFT OUTER JOIN Live2007.SITE T5 ON T1.ARTNR1 = T5.ARTIKELNUMMER AND T5.CATEGORIE = 'ARTIKEL' WHERE T1.SENT_TO_GR = 'N' AND T1.DELIVER_REQ = 'Y' AND T1.OTYPE1 <> 'MO' AND T1.LEVERING_EDI = 'Y' UNION ALL SELECT /* snip about 100 lines */ FROM Live2007.ORDERSET T1 INNER JOIN Live2007.SITE T2 ON T2.STAMP1 = T1.STAMP1 LEFT OUTER JOIN Live2007.SITE T4 ON T1.GROSID_2 = T4.ID LEFT OUTER JOIN Live2007.SITE T5 ON T1.ARTNR2 = T5.ARTIKELNUMMER AND T5.CATEGORIE = 'ARTIKEL' WHERE T1.SENT_TO_GR = 'N' AND T1.DELIVER_REQ = 'Y' AND T1.OTYPE2 <> 'MO' AND T1.LEVERING_EDI = 'Y' UNION ALL /* snip 25 unions almost identical to the 4 left here. */ UNION ALL SELECT /* snip about 100 lines */ FROM Live2007.ORDERSET T1 INNER JOIN Live2007.SITE T2 ON T2.STAMP1 = T1.STAMP1 LEFT OUTER JOIN Live2007.SITE T4 ON T1.GROSID_29 = T4.ID LEFT OUTER JOIN Live2007.SITE T5 ON T1.ARTNR29 = T5.ARTIKELNUMMER AND T5.CATEGORIE = 'ARTIKEL' WHERE T1.SENT_TO_GR = 'N' AND T1.DELIVER_REQ = 'Y' AND T1.OTYPE29 <> 'MO' AND T1.LEVERING_EDI = 'Y' UNION ALL SELECT /* snip about 100 lines */ FROM Live2007.ORDERSET T1 INNER JOIN Live2007.SITE T2 ON T2.STAMP1 = T1.STAMP1 LEFT OUTER JOIN Live2007.SITE T4 ON T1.GROSID_30 = T4.ID LEFT OUTER JOIN Live2007.SITE T5 ON T1.ARTNR30 = T5.ARTIKELNUMMER AND T5.CATEGORIE = 'ARTIKEL' WHERE T1.SENT_TO_GR = 'N' AND T1.DELIVER_REQ = 'Y' AND T1.OTYPE30 <> 'MO' AND T1.LEVERING_EDI = 'Y' GO
Anyone interested in the whole view can find it here: http://www.4shared.com/document/O-poA33U/Anyway.htm
-
RE: The most proper use of a sql cursor ever
It could be worse.
I could be the a**hole that wrote this and be hated by all ;-) Since I didn't write it I am just hated by some
-
RE: The most proper use of a sql cursor ever
Not in Utah, just down here in the Netherlands.
There are a couple of hundred users using this database. This particular cursor is at the end of a 500+ line sp that does one update after another without a transaction which often leads to corrupted data.
The proces for every table is:- truncate the table
- insert some product ids
- run a seperate update statement for every single column in the table (most of the tables are 10 columns or so)
The import proces itself is started by pressing a button in an msaccess frontend and takes an hour or so importing about 12 different files. after that 2 sp's like the one described in here are used to fill some staging tables and one more to fill the eventual tables that hold the data used for the reports they need.
THe worst thing is that this is one of 3 dozen applications we need to maintain for this customer and this little bit is kind of exemplary for the whole lot of them :-(
- truncate the table
-
RE: The most proper use of a sql cursor ever
Just to clarify,
This is MS SQL Server 2005
@LC is Initialized and given an initial value of 1
this should have been a identity column with a seed of 1 and increment of 1 and one single insert command.
It is just one of the atrocities that I come across at this clients code base.