Query On Datediff Function
May 18, 2007
Hi,
I am trying to use DataDiff function and I have used the following queries:
1.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test
Expected Result: 0 milliseconds
Actual Result: 0 milliseconds
2.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test
Expected Result: 1 milliseconds
Actual Result: 0 milliseconds
3.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test
Expected Result: 2 milliseconds
Actual Result: 3 milliseconds
4.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test
Expected Result: 3 milliseconds
Actual Result: 3 milliseconds
5.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test
Expected Result: 4 milliseconds
Actual Result: 3 milliseconds
6.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test
Expected Result: 5 milliseconds
Actual Result: 6 milliseconds
7.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test
Expected Result: 6 milliseconds
Actual Result: 6 milliseconds
8.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test
Expected Result: 7 milliseconds
Actual Result: 6 milliseconds
9.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test
Expected Result: 8 milliseconds
Actual Result: 6 milliseconds
10.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test
Expected Result: 9 milliseconds
Actual Result: 10 milliseconds
Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.
Thanks,
Tim
View 1 Replies
ADVERTISEMENT
Apr 9, 2007
Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh
View 3 Replies
View Related
Nov 23, 2006
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...
View 2 Replies
View Related
Nov 12, 2007
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.
View 3 Replies
View Related
Apr 25, 2007
hi
select datediff(m,'3/5/2003',getdate as experience
output is
Experience
----------
49
select (datediff(m,'3/5/2003',getdate()))/12 as experience
output is
Experience
----------
4
but 49/12 is 4.08333=4.1
how i can get this
Actually my task is to output as years.months
Thanks in advance
Malathi Rao
View 7 Replies
View Related
Oct 5, 2006
I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.
use 'Database'
Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",
datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"
from 'Table'
WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR
DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR
status = 'Previous' OR
status = 'non-billable')
View 5 Replies
View Related
Feb 7, 2007
This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart
(
@FromTime DATETIME,
@ToTime DATETIME
)
RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT)
AS
BEGIN
DECLARE@Temp DATETIME,
@Mts INT,
@year SMALLINT,
@month TINYINT,
@day TINYINT,
@hour TINYINT,
@minute TINYINT,
@second TINYINT,
@millisecond SMALLINT
IF @FromTime > @ToTime
SELECT@Temp = @FromTime,
@FromTime = @ToTime,
@ToTime = @Temp
SET@Mts =CASE
WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
ELSE -1
END + DATEDIFF(month, @FromTime, @ToTime)
SELECT@year = @Mts / 12,
@month = @Mts % 12,
@Temp = DATEADD(month, @Mts, @FromTime)
SELECT@day = datediff(hour, @Temp, @ToTime) / 24,
@Temp = DATEADD(day, @day, @Temp)
SELECT@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
@Temp = DATEADD(hour, @hour, @Temp)
SELECT@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
@Temp = DATEADD(minute, @minute, @Temp)
SELECT@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
@Temp = DATEADD(second, @second, @Temp),
@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)
INSERT@Time (year, month, day, hour, minute, second, millisecond)
SELECT@year,
@month,
@day,
@hour,
@minute,
@second,
@millisecond
RETURN
ENDAnd to test the functionSELECTd.FromDate,
d.ToDate,
x.*
FROM(
SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
SELECT'19991231', '20000101' UNION ALL
SELECT'20070207', '20070208' UNION ALL
SELECT'20000131', '20000228' UNION ALL
SELECT'20070202', '20070201' UNION ALL
SELECT'20070207', '20070307' UNION ALL
SELECT'20000131', '20000301' UNION ALL
SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
SELECT'20000131', '20000229'
) AS d
CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x
ORDER BYd.FromDate,
d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond
19690906197606086920000
19991231200001010010000
200001312000022800280000
200001312000022900290000
20000131200003010110000
20011231 15:24:13.08020020101 17:15:56.34300115143263
20011231 17:15:56.34320020101 15:24:13.08000022816736
20020101 15:24:13.08020011231 17:15:56.34300022816736
20070202200702010010000
20070207200702080010000
20070207200703070100000
Peter Larsson
Helsingborg, Sweden
View 14 Replies
View Related
Feb 9, 2007
I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)
sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47
View 4 Replies
View Related
Nov 2, 2007
recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year
within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is
mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted
View 2 Replies
View Related
Jan 25, 2008
I want to create and expression that basically says if a datetime value is today minus 2 days then do another formula.
Right now it looks like this...
Code Snippet
=iif(Fields!Channel_Id.Value="SFE1" and Fields!Report_Date.Value=Today-2,0,(Fields!Total_Apps.Value-Fields!total_apps_null_status.Value))
Basically, it spits an error saying [BC30452] Operator '-' is not defined for types 'Date' and 'Integer'.
Should this be done with DateDiff? if so, how can you specify two days ago?
Thanks much
C-
View 5 Replies
View Related
Feb 3, 2015
I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as
DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]
I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.
View 2 Replies
View Related
May 15, 2007
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
View 3 Replies
View Related
Sep 12, 2015
When running a query such as this on a database view:
select count(*) from WWALMDB.dbo.v_AlarmEventHistory2
SQL throws the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Oddly on a Table the count(*) function works.
Is there a limit on views or the count(*) function that I am not aware of?
View 3 Replies
View Related
Feb 12, 2008
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View 6 Replies
View Related
May 2, 2015
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
View 4 Replies
View Related
Aug 7, 2015
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2 or DateTime1 > DateTime2 .
The DateTime1 and DateTime2 values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
View 3 Replies
View Related
Mar 26, 2014
I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).
I need the difference between two dates in hh:mm:ss format.
I tried following query
declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */
This works fine but as you can see the date I have used in the query is in yyyymmdd format.
The query does not work with yyyyddmm format
The expected answer is =25:01:01.
View 5 Replies
View Related
Mar 5, 2014
ItemIDItemNamePrice
1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00
the above is the existing table and i need a query to retrieve max price with out using max function and sub query
View 6 Replies
View Related
Jan 16, 2008
Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.
Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String) Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType) Dim Connection As New SqlConnection(ConnectionString) Dim Command As New SqlCommand(QueryString, Connection) Command.Connection.Open() Return Command.ExecuteReader()End Function
It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).
Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!
View 2 Replies
View Related
Oct 19, 2004
When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
Bjorn
View 2 Replies
View Related
Feb 15, 2006
hi
i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..
chakri
View 14 Replies
View Related
May 8, 2008
i have function:
can anybody help me to figure this issue? how can i get that.
Thank you.
View 2 Replies
View Related
May 29, 2008
Hi...
I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Incorrect syntax near '.'.
------------------------------
ADDITIONAL INFORMATION:
Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
SELECT dv.product ,
dv.itemname ,
dv.u_vlgx_plc,
dv.shorted ,
dv.onhand ,
dv.po_num ,
t10.docduedate
FROM
(SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname AS t6_cardname,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
) dv
OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10
GROUP BY dv.product ,
dv.itemname ,
dv.u_vlgx_plc ,
dv.shorted ,
dv.onhand ,
t10.docduedate,
dv.po_num ,
dv.t6_cardname
ORDER BY dv.u_vlgx_plc,
dv.t6_cardname,
dv.product
I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.
Any idea how I can fix this? Thanks!
View 2 Replies
View Related
Jan 15, 2014
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
View 3 Replies
View Related
Dec 17, 2007
Hello,
I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.
SELECT
A.ParentSubjectName,
A.ParentSubject,
A.SubjectId,
B.CreatedOn
FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId
WHERE A.ParentSubjectName LIKE 'ACDelco Products%'
AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')
AND A.SubjectId IN
(
'C44ADE3E-527B-DC11-8A2D-00170857BDE7',
'F8758E52-527B-DC11-8A2D-00170857BDE7',
'7E65F458-527B-DC11-8A2D-00170857BDE7',
'7F65F458-527B-DC11-8A2D-00170857BDE7',
'2BE35262-527B-DC11-8A2D-00170857BDE7',
'2AE35262-527B-DC11-8A2D-00170857BDE7',
'A2002127-527B-DC11-8A2D-00170857BDE7',
'41A8A66F-527B-DC11-8A2D-00170857BDE7',
'A3002127-527B-DC11-8A2D-00170857BDE7',
'D6C08B45-527B-DC11-8A2D-00170857BDE7',
'C439FB4B-527B-DC11-8A2D-00170857BDE7'
)
ORDER BY B.CreatedOn[/blue]
View 3 Replies
View Related
Feb 27, 2008
CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)
--Returns a result set that lists all the copds
AS
BEGIN
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)
-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO
i m getting error that Incorrect syntax near the keyword 'WITH'.
can anyone tell me how to join functions in sql?
thanks.
View 4 Replies
View Related
Mar 14, 2008
create function mytotalcount
(@audit varchar(50), @startdate datetime, @enddate datetime)
returns table
as
return
(
select t.value,sum(t.countvalue) as totalcount from
(
select
sm.value,count(sm.value) as countvalue
from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid
inner join audit a
on s.subjectid=a.subjectid
inner join incidentbase i
on i.subjectid=s.subjectid
where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112
group by sm.value
) t
group by t.value
)
value totalcount
------------------
NHLBI Employee329
NIH Employee329
Public329
VIP329
instead of different values i m getting same...
there is something wrong in joins..can anyone help me?
thanks.
View 2 Replies
View Related
Feb 24, 2006
I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers
View 4 Replies
View Related
Apr 26, 2006
What SQL Function Criteria string replaces [forms]![myForm].[myField]?
I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.
View 1 Replies
View Related
Feb 24, 2008
Code Snippet
Declare @DBName as varchar(100)
Declare @Query as varchar(8000)
SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue
Set @Query ='
SELECT
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice),
a.MoeenName_L1
FROM
ReciptItems LEFT OUTER JOIN
' + @DBName + '.dbo.Categories AS a
ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
a.MoeenName_L1'
Exec (@Query)
View 10 Replies
View Related
Apr 22, 2008
Hey everybody!
I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function.
I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )
Can anyone direct me to an example, or write a small sample?
Thanks a lot in advance!!!
View 15 Replies
View Related
Mar 30, 2007
I have a SQL database that has a function that returns an id value from a table after you pass in a text variable. I would like to test this functionality in Query Analyzer but when I try to do it this way:
exec dbo.fnc_ORGUNIT_GetByName 'Dummy'
It just says 'Query executed successfully' without any resutls in the results pane. What am I doing wrong?
Thanks!
View 2 Replies
View Related
Jul 27, 2005
I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
e.NewValues.Add("@AccessoryID",
AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)
("AccessoryID"))
e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub
For some reason, I get an error message like this:
Server Error in '/' Application.
Disallowed
implicit conversion from data type nvarchar to data type smallmoney,
table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT
function to run this query.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
Disallowed implicit conversion from data type nvarchar to data type
smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use
the CONVERT function to run this query.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change the
field type within my database with no success, I keep on getting the
same error either way.
I would be very greatful if anybody can help me.
View 2 Replies
View Related