Not according to OP requirements.

He is only storing dates with "no time information", ie time set to 00:00:00

And he wants dates for January 1st of every year to remain to...

Sergiy, that will not work for January 1st dates.

However, you are not that far from a working suggestion.

Try this

DECLARE@Sample TABLE

(

Eno int,

Date datetime

)

Insert@Sample

select 1,'2006-06-30' union all

select 2,'2004-05-31' union all

select 3,'2007-01-01' union...

Jacob Luebbers (7/29/2008)

---

How likely is a collision? Probably not very - but can your process accept this small chance of inconsistency? If so great, if not consider alternatives like using...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832

Also see similar behaviour here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109678

DECLARE@Sample TABLE

(

ID INT PRIMARY KEY CLUSTERED,

Product VARCHAR(10),

DateValue DATETIME,

Value MONEY

)

INSERT@Sample

SELECT 1, 'Yak', 'Oct 17 2007 12:00AM', 0 UNION ALL

SELECT 2, 'Yak', 'Oct 17 2008 12:00AM', 2 UNION ALL

SELECT 4,...

Something similar to this

CREATE TRIGGER dbo.trgMyTrigger ON Crawler_Depth

AFTER INSERT

AS

SET NOCOUNT ON

UPDATEcd

SETcd.Hierarchy = cd.Hierarchy + CAST(cd.pkColumnNameHere AS VARCHAR(100)) + '.'

FROMCrawler_Depth AS cd

INNER JOINinserted AS i ON i.pkColumnNameHere = cd.pkColumnNameHere

DECLARE@Sample TABLE (c CHAR(1))

INSERT@Sample

(

c

)

SELECTCHAR(Number)

FROMmaster..spt_values

WHEREType = 'P'

AND Number BETWEEN 65 AND 90

SELECTc,

CHAR(65 + (ASCII(c) - 64) % 26)

FROM@Sample

There are other ways to obfuscate the data.

Yes.

select top 1 DEPTID from @t1

where EMPID = a.EMPID and WEFDATE <= a.DATE

order by WEFDATE desc

But there is still the...

It will work! 😛

ISNUMERIC('Spagetti') equals to 0.

With that attitude, please let him use cursors...

Add this sample data

UNION ALL

SELECT '$1500,0'

and run your query again...

Or you can pass dates according ISO standard?

EXECUTE dbo.usp_ECL_Stock_Receipts '20080625', 'John Smith'

never fails.

On large tables, there might be a performance issue due to the "triangular join" in the correlated subquery.