User Profile

galexyus · Oct 18 '06, 03:56 PM

Or you can create a log table and add an insert into the log to the trigger with any additional info you might need....

galexyus · Oct 17 '06, 06:54 PM

and to answer your 2 other questions...

To get the month number, you can use this function:

Code:

CREATE FUNCTION ToMonth(@StrMonth CHAR(3))
RETURNS TINYINT
AS
BEGIN
	DECLARE @Month TINYINT, @FormattedDate VARCHAR(20)
	SET @FormattedDate = @StrMonth + ' 1 2000'
	SET @Month = MONTH(CAST(@FormattedDate AS DATETIME))
	RETURN @Month
END

To get number of days...

galexyus · Oct 17 '06, 06:24 PM

Hi, LimaCharlie,

You can use this UDF function:

Code:

CREATE FUNCTION ToDate(@StrDate VARCHAR(20))
RETURNS DATETIME
AS
BEGIN
	DECLARE @Dt DATETIME, @FormattedDate VARCHAR(20)
	SET @FormattedDate = LEFT(@StrDate, 3) + ' ' + RIGHT(@StrDate, 1) + ' 20' + SUBSTRING(@StrDate, 4, 2)
	SET @Dt = CAST(@FormattedDate AS DATETIME)
	RETURN @Dt
END

Then...

galexyus · Oct 10 '06, 01:27 PM

Preincrement ++ has higher precedence than +. That's why in your expression

Code:

a= ++a + ++a

all the ++ operations are performed first, making a = 12, after that a = a + a = 24;

galexyus · Oct 9 '06, 05:09 PM

The problem is that in your odd function you return odd(n-2) for odd numbers. This makes it impossible for the function to meet the stopping condition n==0, because odd number - 2 would always be odd. And the crazy numbers are caused by underflow. Since you are using unsigned int, if you subtract 2 from 1, you will get the largest odd unsiged int 429496295 and so on.

This code will do what you need:

Code:

#include

...

galexyus · Oct 9 '06, 03:56 AM

Hey,

Use this instead:

Code:

DECLARE @ptrval varbinary(16)


SELECT @ptrval = TEXTPTR(LOGG)
FROM GAMES
WHERE ID = 21

UPDATETEXT GAMES.LOGG @ptrval NULL NULL 'your text'

And varchar can be 8000 long.

galexyus · Oct 6 '06, 01:32 PM

Code:

You can use this UDF:

CREATE FUNCTION SumCSV(@CSV AS VARCHAR(1000))
RETURNS INT
AS
BEGIN

DECLARE @Sum INT, @CurNumber INT, @CommaIndex INT
SET @Sum = 0

DECLARE @CurNumStr VARCHAR(20)


WHILE LEN(@CSV) > 0
BEGIN

	SET @CommaIndex = CHARINDEX(',', @CSV)
	IF @CommaIndex = 0 SET @CommaIndex = LEN(@CSV)+1
	SET @CurNumStr

...

galexyus · Oct 5 '06, 12:49 AM

Hey,

If you are using Enterprise manager, double click the package( or right click on the package, there will be a "Design package" option), it will open DTS Package designer, there you will find a menu under the main menu, click on Package and there will be a Save as... command there

galexyus · Oct 5 '06, 12:45 AM

Will this work?

Code:

SELECT * FROM Table t1
WHERE CreateDate = (SELECT MAX(CreateDate) FROM Table t2 WHERE t1.ProductCode = t2.ProductCode)
ORDER BY ProductCode

galexyus · Oct 5 '06, 12:16 AM

hey,

see if this is gonna work for you:

Code:

DECLARE @YEAR1 NVARCHAR(10),@MONTH1 NVARCHAR(10),@SQLJOINYR NVARCHAR(50),@SQLJOINMN NVARCHAR(50),
@CS NVARCHAR(1), @SQL NVARCHAR(1000),@CSTYPE NVARCHAR(50),@cntA int
SET @YEAR1 = '2004'
SET @MONTH1 = '04'
SET @CS = 'P'
SET @SQLJOINYR = ' AND year(A.joining_date)= (''' + @year1 + ''')'
SET @SQLJOINMN = ' and month(A.joining_date)=

...

galexyus · Oct 2 '06, 02:24 PM

You have to use LEFT JOIN instead of WHERE:

Code:

Select a.[Weeknum], b.[No Severity], c.[Severity 3], d.[Severity 2], e.[Severity 1]
From (SELECT Weeknum
FROM [BES$]
Group by Weeknum)
A
LEFT JOIN
(SELECT Weeknum, Count (*) AS [No Severity]
FROM [BES$]
Where Severity = 'No Severity'
And Team = 'AS400'
Group by Weeknum)
b ON a.Weeknum = b.Weeknum
LEFT JOIN

...

galexyus · Oct 1 '06, 02:27 PM

Still doesn't work, the problem is that when I run this query:

Code:

SELECT ID, TalkTime
FROM Connect
WHERE TalkTime = 0

the result set doesn't even contain the records that generate #Error.

galexyus · Oct 1 '06, 01:47 AM

you use full name when accessing tables, for example:

SELECT * FROM DB1.dbo.Table1

DELETE FROM DB2.dbo.Table2

galexyus · Oct 1 '06, 01:37 AM

How about:

Code:

SELECT Files_id
FROM FILETRANS t1
WHERE Filetransdate = (SELECT MAX(Filetransdate) FROM FILETRANS t2 WHERE t1.Files_id = t2.Files_id)
AND Filestatus_id = 2

...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

#Error when applying DatePart function to a datetime field