In vb.net what kind of data can I inject in a smallInt lenght 2 Data Type? Right now I am trying to pass an integer to my SP and it gives me the error "Error converting data type nvarchar to smallint" So I guess that I am not passing the right type of data or I have to cast it? Thanks
I have a dbfield which holds a smallint value for the time eg. 1406 . I have to add this value to a datetime value in another field but need the result as a datetime value. The result would be time(int) + date(datetime) = datetime(datetime). I need this to compare the hours between delivery and dispatch. Thanks for your help P
For a SQL statement in an Alias column I am am combing severalcolumns.But I am having problems with one column as it is a smallint.I get this errorSyntax error converting the nvarchar value to a column of data typesmallintMy Sql statement "Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColNamefrom Table1I was wondering is there anyway to format/convert the smallint tonvarchar, without changing the database.
Newbie question regarding a db I have inherited. A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.DocNo has numbers that represent persons. SequenceNo has numbers that represent specific documents associated with each person (DocNo).So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.My SELECT statement looks like this:SELECT ReadingNo, SequenceNoThis returns data like this: 5 3I would like to concatenate the SELECT statement to return like this: 5-3So I made Sql like this:SELECT ReadingNo + '-" + SequenceNo Which returns a alias ('No Column Named') result value of 8 which is an arithmetic result instead of a string concatination that I want.So my questions are:1. Should the original database designer have used string data types for these columns since they will never be used for math purposes?2. Do I need to cast them to string data type (like nchar(4) - since neither column will ever exceed 4 digits) to get the result I desire?3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?
Is an index based on a smallint (16 bit) really faster than an index based on an int (32 bit) If so, how much...
Four tables
Table A: ID smallint (PK) Text varchar(50)
Table B:ForeginID smallint (indexed - non unique) Text varchar(50) rowID int (PK)
Table C: ID int (PK) Text varchar(50)
Table D:ForeginID int (indexed - non unique) Text varchar(50) rowID int (PK)
Table A and C contain identical data Table B and D contain identical data (Tables A and B were filled and then copied to Tables C and D)
Tables A/C are loaded with 64,000 records (-32,000 to 32,000) Tables B/D are loaded with 6,400,000 records ForeginID loaded randomly with values between -32,000 and 32,000
The purpose of this test is to find out if identical queries joined on a smallint are actually faster than int based.
I ran 3 queries on each set: - Full select - Select on ID/Foregin ID - Select on Table2 RowID joined to table 1
Here are the queries:
#1. Full select (smallint) - grouped to limit result set ----------------------------------------------------------- SELECT intAID, COUNT(intBID) FROM TESTintA INNER JOIN TESTintB ON intAID = intBID GROUP BY intAID ORDER BY COUNT(intBID) desc
#2. Select on ID/Foregin ID (smallint) ------------------------------------------ SELECT intAID, intBID, strATXT, strBTXT FROM TESTintA INNER JOIN TESTintB ON intAID = intBID WHERE intAID = 29120
#3. Select on Table2 RowID joined to table 1 (smallint) ------------------------------------------ SELECT intAID, intBID, strATXT, strBTXT FROM TESTintA INNER JOIN TESTintB ON intAID = intBID WHERE intPK = 1050
#4. Full select (int) - grouped to limit result set ------------------------------------------ SELECT lngCID, COUNT(lngDID) FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID GROUP BY lngCID ORDER BY COUNT(lngDID) desc
#5. Select on ID/Foregin ID (int) ------------------------------------------ SELECT lngCID, lngDID, strTXTC, strTXTD FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID WHERE lngCID = 29120
#6. Select on Table2 RowID joined to table 1 (int) ------------------------------------------ SELECT lngCID, lngDID, strTXTC, strTXTD FROM TESTlngC INNER JOIN TEXTlngD ON lngCID = lngDID WHERE intPK = 1050
Here are the results: (run multiple times to verify)
#1. Full select (smallint) - grouped to limit result set ----------------------------------------------------------- (8 seconds) - before computing statistics on table (13 seconds) - after computing statistics on table
#2. Select on ID/Foregin ID (smallint) ------------------------------------------ (0 seconds)
#3. Select on Table2 RowID joined to table 1 (smallint) ------------------------------------------ (0 seconds)
#4. Full select (int) - grouped to limit result set ------------------------------------------ (8 seconds) - before computing statistics on table (7 seconds) - after computing statistics on table
#5. Select on ID/Foregin ID (int) ------------------------------------------ (0 seconds)
#6. Select on Table2 RowID joined to table 1 (int) ------------------------------------------ (0 seconds)
Conclusion: Not only is there a negligible difference in select performance, generating stats on the smallint actually makes it slower. (perhaps there is some kind of conversion going on here behind the scenes?)
I modified it to accept NULL values and conform more closely to INT specification. Here is my modified function:
CREATE FUNCTION [dbo].[udfIsValidINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @ShiftByOne INT; IF LEFT(@Number, 1) = '-' SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1; SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0) IF (PATINDEX('%[^0-9-]%', @Number) = 0 AND CHARINDEX('-', @Number) <= 1 AND @Number NOT IN ('.', '-', '+', '^') AND LEN(@Number)>0 AND LEN(@Number)<11 AND @Number NOT LIKE '%-%') SELECT @Ret = CASE WHEN CONVERT(BIGINT,@Number) - @ShiftByOne <= 2147483647 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidINT('2147483648') SELECT dbo.udfIsValidINT('2147483647') SELECT dbo.udfIsValidINT('-200') SELECT dbo.udfIsValidINT('-2147483649') SELECT dbo.udfIsValidINT('32900') SELECT dbo.udfIsValidINT('1.79E+308') GO
I also have a separate function for SMALLINT:
CREATE FUNCTION [dbo].[udfIsValidSMALLINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @ShiftByOne INT; IF LEFT(@Number, 1) = '-' SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1; SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0) IF (PATINDEX('%[^0-9-]%', @Number) = 0 AND CHARINDEX('-', @Number) <= 1 AND @Number NOT IN ('.', '-', '+', '^') AND LEN(@Number)>0 AND LEN(@Number)<6 AND @Number NOT LIKE '%-%') SELECT @Ret = CASE WHEN CONVERT(INT,@Number) - @ShiftByOne <= 32677 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidSMALLINT('589') SELECT dbo.udfIsValidSMALLINT('-200') SELECT dbo.udfIsValidSMALLINT('-32900') SELECT dbo.udfIsValidSMALLINT('32900') SELECT dbo.udfIsValidSMALLINT('1.79E+308')
and one for TINYINT:
CREATE FUNCTION [dbo].[udfIsValidTINYINT] ( @Number VARCHAR(100) ) RETURNS BIT BEGIN DECLARE @Ret BIT, @L TINYINT; SET @L = LEN(@Number); SET @Number = COALESCE(@Number,'0'); IF (PATINDEX('%[^0-9]%', @Number) = 0 AND @L>0 AND @L<4) SELECT @Ret = CASE WHEN CONVERT(SMALLINT,@Number) < 256 THEN 1 ELSE 0 END ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidTINYINT('256') SELECT dbo.udfIsValidTINYINT('-1') SELECT dbo.udfIsValidTINYINT('0') SELECT dbo.udfIsValidTINYINT('255') SELECT dbo.udfIsValidTINYINT('1.79E+308')
And, finally, a separate function for DECIMAL validation:
CREATE FUNCTION [dbo].[udfIsValidDECIMAL] ( @Number VARCHAR(100), @Scale TINYINT, @Precision TINYINT ) RETURNS BIT BEGIN DECLARE @Ret BIT, @L TINYINT, @DSI TINYINT; SET @Number = COALESCE(@Number,'0'); IF LEFT(@Number, 1) = '-' SELECT@Number = SUBSTRING(@Number, 2, LEN(@Number)); SET @L = LEN(@Number); SET @DSI = @L - LEN(REPLACE(@Number,'.','')) IF( PATINDEX('%[^0-9.]%', @Number) = 0 ANDCHARINDEX('-', @Number) = 0 AND@DSI <= 1 AND@L>0 AND@L<=@Scale+@DSI+ CASE @DSI WHEN 1 THEN @L-CHARINDEX('.', @Number) ELSE 0 END AND @Scale - @Precision >= CASE @DSI WHEN 1 THEN CHARINDEX('.', @Number) - 1 ELSE @L END ) SELECT @Ret = 1 ELSE SET @Ret = 0 RETURN @Ret END GO SELECT dbo.udfIsValidDECIMAL('256',2,0) SELECT dbo.udfIsValidDECIMAL('-1',1,0) SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,17) SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,16) SELECT dbo.udfIsValidDECIMAL('-255.0000000000000001',3,0) SELECT dbo.udfIsValidDECIMAL('1.79E+308',9,2)
Node that the DECIMAL validation function specifically tests whether the input number can legally convert to a given decimal scale and precision. Converting a value of 0.234234 over to DECIMAL(1,0) will work, but SQL will truncate the actual decimals to fit it in that space. However, it will throw an error if you have too many whole digits.
On the whole, I was rather rushed to get these created, so there may be some errors I didn't notice. I'm interested in any improvements you guys can make to improve performance or make them cleaner.
I need to insert rows into a table which contains a smallint field for time. The times are stored in that colum as integers (898, 11345, 1259, etc.) How can I enter a time like 9:15 AM into this field? I know how to display integer data in hh:mm format but I'm stumped on how I can do the reverse.
Sql Server has many data types. For Example: smallint Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes. I want to know that If it contains like 0 or 100 or 1000 or -200 or -2000 or more or less. What will its actual size? 2 bytes or change with the value. Please also mention the reference with your answer. if available.
A SQL Server 2005 db has three date related-columns (MonthGiven, DayGiven, YearGiven) each as smallint datatype. I would like to create a DocDate column (datetime datatype) that combines the data from the three existing date-related columns. I have tried casting and simple concatentation without success. ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime) I think I need to be doing a Conversion instead of casting but have been unable to implement info I have found in the SQL Server Developer Center in my situation.
I need to change the datatype of a very large table from smallint to int... What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..
UPDATE P SET P.IsError=1 ,P.IsDrawingRevNo=1 ,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)' FROM ZPTSMGR.ProjectDrawingRaw P WHERE P.LogId=@LogId AND P.ProjectId=@ProjectId AND P.Revision > 32767 (P.Revision NOT BETWEEN -32768 AND 32767) --SMALLINT RANGE -32768 to 32767.
--DataType Range --tinyint DataType (MinVal: 0, MaxVal: 255). Its storage size is 1 byte. --smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes. --int DataType -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes. --Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value
If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...
Declare @todays_date datetime
Select from person CASE WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1 ELSE datediff (year, Date_Of_Birth, @Todays_Date) END >= 18
I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????
SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1
The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!
I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.
I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.
Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,* FROM weekendtest
i know i could do SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,* FROM weekendtest
but i was just wondering if there some other function i should use that would be more appropriate
Hi I've a problem!!! i want to use datediff thats no problem. But the first parameter has to be parameterised. What datatype do i declare it as. i declare it as varchar it returns me an error : Invalid parameter 1 specified for datediff.
i'm doing this in a stored procedure.
Is there a way out or do i've to use the good old 'IF' or 'Case' ?
Hi, I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays, sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE()) FROM Details
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00. My code : datediff(Minute,'01:00:00','23:00:00') The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours).... Can anybody help ??? Thanks in advance...
I'm trying to use the same select statement I used in classic SQL, but it isn't working. I get a "The server tag is not well formed." error. I'm trying to return all the records where the date in a particular field are over 60 days old. My select statement is: "SELECT [ReNewAd], [Male or Female], [File2], [PHOTO], [First Name], [Last Name], [City], [State], [NewAd] FROM Members Where ('NewAd' IS NOT NULL AND (DateDiff('d', [NewAd], '" & Now() & "') <= 60)) order by [NewAd] Desc" I've been looking through the datediff posts, but I'm not making any sense of what I'm reading. Can anyone see what I'm doing wrong? Thanks, Diane
I'm trying to create a stored procedure that will return a set of data. An input parameter (if specified) will determine what topics will be show based on the date the item "datetime". ALTER PROCEDURE [dbo].[discussions_GetTopics]@board_id as int,@PageNumber INT,@TopicsDays INT = NULL,@TopicsPerPage INT,@HowManyProducts INT OUTPUTAS-- Declare the table that will store all the topics for the given board_idDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)-- populate the tableINSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY discussions_topics.topic_id), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDCASE @TopicsDays WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1 WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7 WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14 WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30 WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90 WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180 WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365END)-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(topic_id) FROM @Topics-- Return the specified page of topicsSELECT * from @TopicsWHERE RowNumber > (@PageNumber - 1) * @TopicsPerPageAND RowNumber <= @PageNumber * @TopicsPerPage I need help where the "CASE @TopicsDays WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1 WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7 WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14 WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30 WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90 WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180 WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365 END" Is... Im not doing it right. Can someone help me?
hello, i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive... i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on the pictures added in database are active (available to be seen by users) only 1yaer after the date added I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded FROM Pictures WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2) ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me
Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt.
This is a sql question, so I'm hoping someone can give me some direction. I need to query a table and return all rows where the individual is >= 18 years of age. It should involve simply subtracting the date of birth from the current date. I thought the answer would be so simple: select * from table where datediff(yy,dob,getdate()) >= 18 Unfortunately, the results are inconsistent. The DATEDIFF function, it appears, will not show correct results down to the month/day level. I tried posting this on a sql server forum, but the recommendations I received involved coding some gigantic function. It seems that there ought to be a simple 'WHERE' clause which could solve this. If someone knows of a silver bullet, I would surely appreciate your advice. Thank you.
I'm trying to include a DATEDIFF compare in my Where clause, but it seems to ignore the comparison I'm trying to invoke, and just returns all rows. When I display the result of the DATEDIFF command, it looks good, but I can't seem to have it incorporated in my Where clause. The following is my code - select [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time] from pkgactions WHERE (8 > DateDiff(year, [End Exec Date], GetDate() )) order by [pkg id]
I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.
My first problem is that I'm not able to show hour AND minutes. I wrote this for instance: SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time I tried HH:mm instead of hour, but it is not working.
My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:
start hour: 10:00 End hour: 11:00 Number of the problem: 1 start hour: 13:00 End hour: 16:00 Number of the problem: 1
So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?
Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).
To find @PurchaseTotal for the year, I have to write the following:
SELECT @PurchaseTotal = Sum(PurchaseTotal) FROM _Expenses WHERE DateDiff(yyyy, DateOf, @IntervalDate) = @Interval
To find @PurchaseTotal for a month, I have to write the following:
SELECT @PurchaseTotal = Sum(PurchaseTotal) FROM _Expenses WHERE DateDiff(mm, DateOf, @IntervalDate) = @Interval
To find @PurchaseTotal for all the records, I have to write the following:
SELECT @PurchaseTotal = Sum(PurchaseTotal) FROM _Expenses
Error says something like "incorrect parameter 1 for DateDiff."
It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.
Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?
I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly? select DateDiff(mm,11/1/2004, 12/31/2005) SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description
Hi im having som problems using the dateDiff, 1. Im not really sure what row's to grap, 2.Im getting this error saying somthing like : "None-spicefied Function GetDate" "sry for the bad translation, im getting the error's in danish and my englich is not to good :)" Any help would be very nice =) ----------------------------------- Code ----------------------------------- This is my clas.
Function NewSearch () As DataTable Dim objData As New DataAccess
Dim strSQL As String = "SELECT datediff(mm, GetDate(), Repair) FROM Used_Cars"
Return objData.GetDataAccess(strSQL, _strDatabase) End Function
And this is my default.aspx.vb .
Protected Sub action_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles action.Click Dim Search As New carFactory Dim dtMaerker As DataTable dtMaerker = Search .NewSearch () For Each Row As DataRow In dtMaerker.Rows ' "Repair" contains som date's lblIndhold.Text = Row.Item("repair") & "<br />" Next End Sub
