SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure
Hi,
I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]
'942' is the unique key column value
However if I update any other column the syntax is fine
The same blanket update query makes the changes no matter what is
updated
The problem only happens when I set a unique key on the date field in
question
Key is a composite of an ID, and 2 date fields
If I allow duplicates in the index it all works perfectly
I am trying to trap 'Duplicate value in index' (which is working on
other non-date columns in other tables)
This is driving me nuts
Any help would be appreciated
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Format Date In A Stored Procedure
Dear friends, I have a stored procedure that returns some fiels. One of the fields is a datetime type. The field return in the follow format : 2006-11-13 0:00:00 How can I return only 2006-11-13? How can I use the format function? regards!!!
View Replies !
View Related
SQL's America Date Format Conflict With Australian Date Format
Hi I am trying get my VB6 application to insert a record into a table (SQL Express) which has a datetime column but it would not process if the data format is differ to *American Date format*. The date() function in VB returns 15/11/2006 which is in Australian Date format (DD/MM/YYYY) according to my setting in "Reginal and Lanuage Option-> Locale 0> English (Australia)" setting. I get the following error: Msg 242, Level 16, State 3, Server KITSQLEXPRESS, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. My computer's locale is set to English (Australia) and I expect the datetime format would follow what is set in system locale I've read an article somewhere on the net about how SQL 2005 eliminate the confusion of date conversion when read/write datetime records into a table...but it seems to me that it is still as in-flexible as MS Access Is there a setting in the database that takes care of it? Thanks
View Replies !
View Related
How To Update A Date With Dd/mm/yyyy Format
How do I update a date with this format? I put HTMLEncode = false and dataformatString = {0:d} so I can just have the date and drop the time. Now it's not updating in the database. Here's my stored procedure: CREATE PROCEDURE [UpdateRtnDate] @loanrequestid int, @returndate datetime AS Update LibraryRequest set[returndate]=@returndatewhere loanrequestid = @loanrequestid It doesn't go into the database what am I misisng?
View Replies !
View Related
Stored Procedure Giving Error When Searching On Date Range
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1] @ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
View Replies !
View Related
Sharepoint Integration With Erroneous Date Format In Date Time Picker
Dear Expert! A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy). Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp €œ12:00 AM€? in the end, making further enquiries to fail. The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears. The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option. The test report has no database connection whatsoever. When setting the site to US, the timestamp won€™t appear at all. The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all. Any advice would be greatly appreciated. Kind Regards Johan Rastenberger
View Replies !
View Related
Date Picker In Web Browser Shows Incorrect Date Format
I am using reporting services, when I go to view my report in Report Manager (web browser is IE7), I choose a date from a date picker control, and the date that populates the date field is in US format mm/dd/yyyy, however in my regional settings, although I have English(United States) I have altered my short date format to be dd/mm/yyyy. Currently my report will display an error saying the date is an invalid format if I pick a date that violates the mm/dd/yyyy format. I want it to display the date format that I have defined in my regional settings, without modifying the 'Language Preference' settings for IE. The report properties has =User!Language for the 'Language' property. Does anyone have any suggestions?
View Replies !
View Related
How To Get Todays Date In Format YY/MM/DD And To Compare It To Another Date Passed Into The Sql
I need to do the following and am hoping someone can help me out. I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to thestored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the datepassed into the stored proc needs to be compared to todays date. Todays date must be determined inthe SQL. So basically here is my pseudo code for what I am trying to accomplish. Basically I just am afterthe comparison of the two values: If @BeginDate < TodaysDate The difficult part is how to obtain the value for "TodaysDate" Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in. Can someone please code this out for me in Microsoft SQL. I would be forever grateful.
View Replies !
View Related
Convert String To Date Independent Of Date Format
Hi, I Have this simple convertion in a Script component Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4)) this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format. but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server. How do I convert the string to date not depending on the SQL server language. thanks.
View Replies !
View Related
Date Time Format For Date Parameter
Hello, I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is. how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000 thank you Kiran
View Replies !
View Related
Error When Using British Date Format
AN error, "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." occurs when I execute this Update statement via Query Analyzer,"update userlog set login = '20/01/2001'". Note: I am using SQL Server 7.0 with us_english as default laguage. Regional settings short date format is already set to british (dd/mm/yyyy) format. I have a friend with the same settings and he can execute the above statement without any problem. What am I missing? Please help me.
View Replies !
View Related
Invalid Date Format Error ... Please Help ...
Hi, I'm using ODBC (System DSN) to connect to SQL Server 7.0 Database. Whenever I try to connect to SQL Server Database from my client application, I'm getting the following error message - ---------------------------------------------------------- Microsoft SQL Server has reported the following error: [Microsoft] [ODBC SQL Server Driver] Invalid Date format ---------------------------------------------------------- After this message, I'm getting another error message - ---------------------------------------------------------- Cannot create a record in table SysConfig (SysConfig). The SQL Database has issued an error. ---------------------------------------------------------- Subsequently, I get another error message - ----------------------------------------------------- [Microsoft][ODBC SQL Server Driver]Invalid date format [INSERT INTO SYSCONFIG (CONFIGTYPE,ID,VALUE,MODIFIEDDATE,MODIFIEDTIME,MOD IFIEDBY,CREATEDDATE,CREATEDTIME,CREATEDBY,RECID ) VALUES (?,?,?,?,?,?,?,?,?,?)] ----------------------------------------------------- The application that I'm trying to connect from is a package from 3rd Party. I do not have any control over it. But I think the problem could be from the side of SQL Server. Can someone look into this please? Thanks, Harish
View Replies !
View Related
Correct Syntax For An Update Stored Procedure
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
View Replies !
View Related
MSDE Update SQL Stored Procedure Syntax
I have 2 tables, table A and B. Table A has the following fields; Phone (nvchar), Fname (nvchar), Lname (nvchar), DNC (bit). Table B has one field, PhoneNo (nvchar). I would like to update the field DNC in Table A to True(1) if the values Phone in Table A = PhoneNo in Table B. I tried this syntax but it never updated: UPDATE dbo.A SET DNC = 1 WHERE (Phone = 'SELECT MAX dbo.B.PhoneNo FROM dbo.B') These tables have over 100K records and I would like to make sure it runs through and checks every single record and not just the first 10000.
View Replies !
View Related
Default Date(current Date) Function W/ Update?
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months). We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL. What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work. Is there a function I can use that can auto-populate for both insert and updates?
View Replies !
View Related
Date Stored Procedure
Hi all,I am trying to write a stored procedure that will delete records in atable that are older then 30 days. I am checking against a field thatis called PositionDate. Can someone point me in the right direction?TIA
View Replies !
View Related
Stored Procedure With Date
There is a stored procedure to run each Sunday. I would like to get the order date from previous Sunday through Saturday. Right now we code as "OrderDate Between convert(datetime, '05/02/99') AND convert(datetime, '05/08/99') " Each week the dates have to be manually changed. How to use the date function to make it automated?
View Replies !
View Related
Group By Date In Stored Procedure
I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this. Select count(recordid),transactiondatefrom sometable group by transactiondate Thanks in advance
View Replies !
View Related
Stored Procedure - Sql Date Select
Having a little trouble getting this one to work. I have a stored procedure that selects items from the table... SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews I need to change it so it selects only the year needed. For instance, only items from 2004. SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews WHERE fldDate = @myParameter -Say myParameter = 2004 or whatever year is needed The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure. How can I be sure to only get a certain year? Thanks, Zath
View Replies !
View Related
Stored Procedure - Date Function
Hi, I ran into some problem here. The case scenerio is supposed to be like this: - Each member can only make one appointment at any one time and only make another appointment after the existing appointment expired. - Each member is allowed to make an appointment at any time and must be at least 5 days in advance. I managed to do the reservation for at least 5 days in advance but I can't allow the member to make only one appointment. The member can keep making appointments even though the existing appointment has not expired. Can someone pls help? Thanks! ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS BEGIN IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC) RETURN -300 BEGIN IF EXISTS (SELECT COUNT(@MemNRIC) FROM DasAppointment WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5) GROUP BY MemNRIC HAVING COUNT(@MemNRIC) <> 0) RETURN -301 ELSE IF EXISTS (SELECT MemNRIC FROM DasAppointment WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE())) RETURN -302 END END INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC) IF @@ERROR <> 0 RETURN @@ERROR RETURN DECLARE @status int EXEC @status = spReserveAppt '2005-08-16', '1900', 'S1256755J' SELECT 'Status' = @status
View Replies !
View Related
Stored Procedure And Date For Today
Hi there, I am trying to ascertain how many users have registered with my site today. I am using the following stored procedure: CREATE Procedure spGetUserCountToday As Return ( SELECT Count(*) FROM tblUserList WHERE role= "User" AND registerDate >= GETDATE() ) GO However the issue is the GetDate() function will only return those that have resgistered at the exact moment the query is run. How can I change the GetDate to return only those users who have registered in?? Thanks in advance, TCM
View Replies !
View Related
Stored Procedure Date Question
The following is part of my stored procedure where I could use some help:If I remark out the two lines regarding dates, it runs fine. The dateparameter is passed from a VB app as a text value in the format mm/dd/yy.Thanks for any help.CharlieSELECT * FROM dbo.TWQRHISTEARDOWN_HEADER where(WQR_TDH_MODELYEAR_T=@Model_Year OR @Model_Year IS NULL)AND (WQR_TDH_OFFLINEDT_Y>=@Start_Date OR @Start_Date IS NULL)AND (WQR_TDH_OFFLINEDT_Y<=@Stop_Date OR @Stop_Date IS NULL)
View Replies !
View Related
Date Range In Stored Procedure
I have a stored procedure (below) to which I pass several input parameters, 2 of which are dates (@in_ToDate and @in_FromDate) to allow me to calculate a date range. When I use the date range in the query I don't get a record set back. The data within the specified date range exists. I know that because the same query works fine and returns the right results when run by it self in the query analyser. It also works ok if I hard code the dates in the stored procedure. I can not work out what I am doing wrong. I would greatly appreciate any help on this The code in question is identified by the /* FIXME */ comment below. Thank you /************************************************** ************** * Procedure: SP_ReportStoreBenchmark * * Author: Jerry Vanicky * * Date: 20/11/2000 * * Description: * * * Table: * * ************************************************** **************/ PRINT 'CREATE PROCEDURE SP_ReportStoreBenchmark' -- section for deleting all version of existing procedure if exists (select * from sysobjects where id = object_id('SP_ReportStoreBenchmark') and sysstat & 0xf = 4) drop procedure SP_ReportStoreBenchmark PRINT 'Existing Procedure SP_ReportStoreBenchmark Dropped!' GO -- create the procedure CREATE PROCEDURE SP_ReportStoreBenchmark ( @in_ObjectIDint, @in_ToDatedatetime, @in_FromDatedatetime, @in_Industryint, @in_MediaFormatTypes int, @in_Genresint, @out_error_codeintOUT,-- error code, 0 if no error found @out_error_messagenvarchar(200)OUT-- error description relating to above error code ) AS DECLARE @l_proc_namenvarchar(100) DECLARE @l_yourStorereal DECLARE @l_yourStoreTempreal DECLARE @l_yourStatereal DECLARE @l_nationalreal DECLARE @l_thisStateIDint DECLARE @l_stateAverageSales int DECLARE @l_nationalAverageSales int DECLARE @l_statePercentreal DECLARE @l_nationalPercentreal DECLARE @l_stateStoreCountint DECLARE @l_nationalStoreCountint DECLARE @l_ToDatedatetime DECLARE @l_FromDatedatetime -- person and store details that are requesting this report DECLARE @l_lastnamenvarchar(50) DECLARE @l_firstnamenvarchar(50) -- the details posted into the report DECLARE @l_industrynvarchar(50) DECLARE @l_genrenvarchar(50) DECLARE @l_categorynvarchar(50) DECLARE @l_totalnvarchar(50) BEGIN -- initialise status parameters SET@out_error_code = 0 SET@out_error_message = 'OK' SET@l_proc_name = 'SP_ReportStoreBenchmark' PRINT '--- SP_ReportStoreBenchmark starts here --- ' PRINT 'Input Parameters Passed Into ' + @l_proc_name + ' Are:' PRINT '@in_ObjectID :' + CONVERT(nvarchar(10), @in_ObjectID) PRINT '@in_ToDate :' + CONVERT(nvarchar(12), @in_ToDate) PRINT '@in_FromDate :' + CONVERT(nvarchar(12), @in_FromDate) PRINT '@in_Industry : ' + CONVERT(nvarchar(10), @in_Industry) PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(10), @in_MediaFormatTypes) PRINT '@in_Genres : ' + CONVERT(nvarchar(10), @in_Genres) -- set the dates to the dd mon yyyy format eg.(1 Jan 2000) SET @l_ToDate = CONVERT(datetime, @in_ToDate, 113) SET @l_FromDate = CONVERT(datetime, @in_FromDate, 113) -- get the person and store details requesting this report PRINT 'Getting the person and store details that are requesting this report.' SELECT @l_firstname = tblPerson.FirstName, @l_lastname = tblPerson.LastName FROM tblObject INNER JOIN tblPerson ON tblObject.PersonID = tblPerson.PersonID WHERE (tblObject.ObjectID = @in_ObjectID) -- get the parameter names used by this report to filter on PRINT 'Get the parameter names used by this report to filter on.' -- the industry name SELECT @l_industry = Name FROM tblLookup WHERE Type = 20 AND id = @in_Industry -- the genre name SELECT @l_genre = Name FROM tblLookup WHERE type = 133 AND id = @in_Genres -- the category name SELECT @l_category = Name FROM tblLookup WHERE type = 25 AND id = @in_MediaFormatTypes -- Task 1 PRINT '<task 1 - get the stateID of this state>' -- retrieve this state id SELECT @l_thisStateID = tblAddress.StateID FROM tblObject INNER JOIN tblContactAddress ON tblObject.ContactAddressID = tblContactAddress.ContactAddressID INNER JOIN tblAddress ON tblContactAddress.AddressBillingID = tblAddress.AddressID WHERE (tblObject.ObjectID = @in_ObjectID) PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the storeID for storeID (' + @in_ObjectID + ').' PRINT 'Unable to retrieve the stateID for storeID (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '<task 2 - sum the sales for your store>' PRINT '@in_FromDate : ' + CONVERT(nvarchar(20), @in_FromDate) PRINT '@in_ToDate : ' + CONVERT(nvarchar(20),@in_ToDate ) PRINT '@in_Industry : ' + CONVERT(nvarchar(20),@in_Industry ) PRINT '@in_MediaFormatTypes : ' + CONVERT(nvarchar(20),@in_MediaFormatTypes) PRINT '@in_Genres : ' + CONVERT(nvarchar(20),@in_Genres ) PRINT '@in_ObjectID ' + CONVERT(nvarchar(20),@in_ObjectID) /* FIXME */ SELECT @l_yourStore = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND StoreID = @in_ObjectID AND PurchaseDate BETWEEN convert(datetime, @in_FromDate, 113) AND convert(datetime, @in_ToDate, 113) PRINT 'The following line should display the store count of the sales.' PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum store sales for storeID (' + @in_ObjectID + ').' PRINT 'Unable to sum store sales for storeID (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '<task 3 sum the sales for your state>' -- get the data for the state SELECT @l_yourState = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND State = @l_thisStateID PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum state sales for stateID (' + @l_thisStateID + ').' PRINT 'Unable to sum state sales for stateID (' + @l_thisStateID + ').' GOTO error_lbl END PRINT '<task 4 sum the sales for the nation>' -- get the national data SELECT @l_national = SUM(SaleAmt) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to sum national sales.' PRINT 'Unable to sum national sales.' GOTO error_lbl END PRINT '<task 5 get the count of the stores for the state>' -- get the state wide count of all stores SELECT @l_stateStoreCount = COUNT(DISTINCT StoreID) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres AND State = @l_thisStateID PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores for the stateID (' + @l_thisStateID + ').' PRINT 'Unable to count stores for the stateID (' + @l_thisStateID + ').' GOTO error_lbl END PRINT '<task 6 get the count of the stores for the nation>' -- get the national count for all the stores SELECT @l_nationalStoreCount = COUNT(DISTINCT StoreID) FROM tblReportStoreBenchmark WHERE (PurchaseDate BETWEEN @l_FromDate AND @l_ToDate) AND Industry = @in_Industry AND FormatType = @in_MediaFormatTypes AND Genre = @in_Genres PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount) IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to count stores nationaly.' PRINT 'Unable to count stores nationaly.' GOTO error_lbl END PRINT 'All varaibles have been set, their values are:' PRINT '@l_thisStateID : ' + CONVERT(nvarchar(10), @l_thisStateID) PRINT '@l_yourStore : ' + CONVERT(nvarchar(10), @l_yourStore) PRINT '@l_yourState : ' + CONVERT(nvarchar(10), @l_yourState) PRINT '@l_national : ' + CONVERT(nvarchar(10), @l_national) PRINT '@l_stateStoreCount : ' + CONVERT(nvarchar(10), @l_stateStoreCount) PRINT '@l_nationalStoreCount : ' + CONVERT(nvarchar(10), @l_nationalStoreCount) PRINT '<task 7>' BEGIN PRINT '<task 7a>' -- get the state average sales SET @l_stateAverageSales = @l_yourState/@l_stateStoreCount PRINT '@l_stateAverageSales: ' + CONVERT(nvarchar(10), @l_stateAverageSales) -- get the national average sales SET @l_nationalAverageSales = @l_national/@l_nationalStoreCount PRINT '@l_nationalAverageSales: ' + CONVERT(nvarchar(10), @l_nationalAverageSales) -- set the statepercentage figure SET @l_statePercent = (@l_yourStore/@l_stateAverageSales)*100 PRINT '@l_statePercent contains: ' + CONVERT(nvarchar(10), @l_statePercent) -- set the national percentage figure SET @l_nationalPercent = (@l_yourStore/@l_nationalAverageSales)*100 PRINT '@l_nationalPercent contains: ' + CONVERT(nvarchar(10), @l_nationalPercent) SELECT @l_yourStore AS storeTtl, @l_stateAverageSales AS stateAverage, @l_nationalAverageSales AS nationalAverage, @l_statePercent AS statePercentage, @l_nationalPercent AS nationalPercentage, @l_FirstName AS FirstName, @l_LastName AS LastName, @l_industry AS industry, @l_genre AS genre, @l_category AS category, @l_total AS total, @in_ObjectID AS objectID, @in_ToDate AS toDate, @in_FromDate AS fromDate --SELECT @l_statePercent AS StatePercent, @l_nationalPercent AS NationalPercent, @l_yourStore AS thisStore, @l_yourState AS thisState, @l_national AS thisNational, IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Error in calculating the total results.' PRINT 'Error in calculating the total results.' GOTO error_lbl END END IF (@@ERROR <> 0) BEGIN SELECT @out_error_code = 1, @out_error_message = @l_proc_name + ': Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').' PRINT 'Unable to retrieve the Benchmark report for the ID of (' + @in_ObjectID + ').' GOTO error_lbl END PRINT '--- SP_ReportStoreBenchmark ends here ---' -- return successfully RETURN(0) error_lbl: IF (@out_error_code = 0) SELECT @out_error_code = 1, @out_error_message= 'Unable to retrieve the Benchmark report.' -- return with errors RETURN(1) END GO PRINT 'Created Procedure SP_ReportStoreBenchmark!' PRINT 'Done...'
View Replies !
View Related
Stored Procedure - Date Problems
I have a stored date in my database, example 12 October 2000 which is a start date - now i would like to retrieve this data and the number of years and months from the current date (that is today - i am using the getdate() method)...how can i get the number of years and months passed? Thanks
View Replies !
View Related
Date Return From Stored Procedure...help!
Hi, I created this stored procedure to add 90 days to the current date, and return the new date: CREATE PROCEDURE Get90Days AS SELECT DATEADD(day, 90, current_timestamp) AS "RETURNDAYS" The procedure above returns this result: 2006-09-29 07:31:14.477 I need this procedure to return the date, only, like this: 09-29-2006 I tried using the convert switch to reformat the result to the date only, but have not been successful. What does this stored procedure need to look like to return just the date, like this: 09-29-2006 I appreciate your help. Thanks, Bill
View Replies !
View Related
ASP Script Update Date Error, Please Help
Datebase user_id int(4) user_name varchar(20) rec_date datetime code <% c_user_id = trim(request("user_id")) c_user_name = trim(request("user_name")) c_rec_date = now() Set rs = Server.CreateObject("ADODB.Recordset") sql = "update ABC.dbo.member set user_name = '"& c_user_name &"', rec_date = #"& c_rec_date &"# where user_id='"& c_user_id &"'" conn.Execute sql conn.close set rsinsert=nothing %> error : 錯誤類型： Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '上�'.
View Replies !
View Related
Stored Procedure To Check Date Range
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck 2 @PID INTEGER = -1 OUTPUT, 3 @PCityFrom Char(3) = '', 4 @PCityTo Char(3) = '', 5 @PDateFrom DATETIME = '31 Dec 9999', 6 @PDateTo DATETIME = '31 Dec 9999', 7 @PIsExists BIT = 1 OUTPUT 8 AS 9 10 CREATE TABLE #TmpControlRequst 11 ( 12 IDINTEGER, 13 IsExistsCHAR(1) 14 ) 15 /*###Pseudo 16 1. Check the Date From and Date To 17 -- select all the value equal to parameter cityFrom and cityTo 18 -- insert the selection records into tmp table 19 --*/ 20 INSERT INTO #TmpControlRequst 21 (ID, IsExists) 22 SELECT ID, 23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 35 ELSE 0 END 36 FROM RequestTable 37 WHERE ID <> @PID 38 AND CityFrom = @PCityFrom 39 AND CityTo = @PCityTo 40 41 --======== FINAL RESULT 42 -- For tmp table:- 43 -- isExists = 1 ==> date lapse 44 -- isExists = 0 ==> date ok 45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting 46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1 47 ELSE 0 END 48 FROM #TmpControlRequst 49 WHEREIsExists = 1 50 51 SELECT @PIsExists 52 --========= 53 54 DROP TABLE #TmpControlRequst 55 56 --========= 57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
View Replies !
View Related
Date Compare In SQL Server Stored Procedure
Hi,i have "req_date" column of "datetime" type in SQL server database tablebesides other columns.From my Web page, i am calling the Stored Procedure with variableparameter "Search_Date" of Varchar(60) type.The value, i am passing to Stored procedure through "Search_Date" iscompared to req_date column of table.My question is that how to do this comparision of date in Selectstatement within Stored Procedure?Thanks
View Replies !
View Related
Time/date In A Scheduled Stored Procedure
Hi there, SQL newbie here, and thanks for any help you may able to provide. My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday. I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it. Thanks again.
View Replies !
View Related
How To Calculate Expiry Date Using Stored Procedure
Hi, i'm trying to calculate on the expiry date for product which include the start date and end date. the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired. the storedprocedure tat i've written is: WHERE DateDiff(day, StartDate, getDate())<=0 AND DateDiff(day, EndDate, getDate())>=0 i wonder why it can's show the result!!! is the code correct?
View Replies !
View Related
Problem Sending Date Parameters To Stored Procedure
Can anyone help, I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report. I am getting the following message: Procedure 'TestReport' expects parameters '@StartDate', which was not supplied. This happens on the objCom.Execute line. This is my code: Dim CRXApplication As New CRAXDRT.Application Dim CRXReport As CRAXDRT.Report Dim CRXDatabase As CRAXDRT.Database Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt") Dim objCom As adodb.Command Set objCom = New adodb.Command Dim prm_one As adodb.Parameter Dim prm_two As adodb.Parameter Dim prm_three As adodb.Parameter Dim fdate, tdate As Date Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu") Set CRXDatabase = CRXReport.Database fdate = cboFrom.Value tdate = cboTo.Value With objCom .CommandText = "TestReport" .CommandType = adCmdStoredProc .ActiveConnection = cn.ConnectionString Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate) .Parameters.Append prm_one Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate) .Parameters.Append prm_two Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2) .Parameters.Append prm_three .Parameters.Refresh End With Set SQLRecs = objCom.Execute CRXReport.DiscardSavedData CRXDatabase.SetDataSource SQLRecs, 3, 1 This is my Stored Procedure: CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1, COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID FROM dbo._Staff INNER JOIN dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode, dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate) GO Thanks in advance Ian.
View Replies !
View Related
Variable Server Name In Stored Procedure - Date Problems
In order to pull back data from several servers to one central server I need to execute a stored procedure over each remote server. SQL 7 doesn't accept the following syntax when comparing date fields: declare @cmd varchar(1000) select @cmd = 'select * from ' + @server + '.DATABASE.dbo.TableName where TableDateField =' + @variableDateField + ' exec (@cmd) I've tried converting both datetime fields to varchar fields and I still get syntax errors. Any suggestions?
View Replies !
View Related
Help-convert Date Inside Stored Procedure Problem
problem convet date Code Snippet DECLARE @y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2) DECLARE @mydate [datetime] set @y_Date='2008' set @M_Date ='8' SET @d_Date='05' set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME) select @mydate 2008-05-08 00:00:00.000 and it look ok but whan i do this Code SnippetEXECUTE [dbo].[testpro] @mydate the date not pass properly !! Code Snippet DECLARE DECLARE @mydate [datetime] set @mydate = getdate() 2008-05-08 01:20:35.870 like this it work ok what is the problem with tis line ? set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS tnx
View Replies !
View Related
Converting Date Data Type In Stored Procedure
HI Experts... I am using SQL SERVER 2005 standard edition I have encountered a problem regarding converting date data type in stored procedure As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e. CREATE PROCEDURE sp_CalendarCreate @StDate VARCHAR(16) , @EDate VARCHAR(16), then I am converting varchar to date with following code DECLARE @STARTDATE DATETIME DECLARE @ENDDATE DATETIME SELECT @STARTDATE = CAST(@STDATE AS DATETIME) SELECT @ENDDATE = CAST(@EDATE AS DATETIME) When I try to execute the procedure with following code execute sp_CalendarCreate @stdate='12-1-06',@edate='20-1-06' but it gives me following error Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Can any one tell me the solution of that problem (at ur earliest) regards, Anas
View Replies !
View Related
|
TRACKER
