Subtracting Datetime In Stored Procedure And Displayed In Hh:mm:ss Format

Nov 22, 2007

 Hi there,
I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).
I was duration in the format  of hours:minutes:seconds.
The stored procedure is pasted below. I am getting the following error.
Syntax error converting datetime from character string. 
 
Any ideas?
 
==============================
 CREATE    PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List  (
    @websiteID AS int = 0,
    @SelectedDateFrom AS dateTime,
    @SelectedDateTo AS dateTime
) AS

IF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0
begin
    set @SelectedDateFrom=null
end

IF ISNULL(@SelectedDateTo, '') = ''
begin
    SET @SelectedDateTo = @SelectedDateFrom
end
SET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)

SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions

WHERE UserID != 'Anonymous'
GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitle
HAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)
GO


==============================
  <Columns>
                                        <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn>
                                        <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn>
                                        <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn>
                                        <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn>
                                        <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn>
                                        <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn>
                                    </Columns>
 
============================ 

View 1 Replies


ADVERTISEMENT

How Can I Format A Datetime Field In A Stored Procedure

Dec 14, 2006

How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?

View 13 Replies View Related

Fields Not Displayed Binding Dataset To Stored Procedure

May 7, 2008

Hi everyone

I am using Stored procedure :

ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End
else If @Num=1 Begin select * from table2 where patientid=@patID End end

While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed.
Please help me out .
Thanks In Advance
Regards
Navdeep

View 20 Replies View Related

Subtracting Periods That Are In YYYYMM Format?

Mar 28, 2014

I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.

Is this possible in the SQL Database?

View 2 Replies View Related

Number Are Displayed In The Wrong Format

Jul 3, 2007

 
HiI'm not sure if this is a .net or a SQL issue. My development machine is using SQL 2005 while the live server is SQL2000. I have a smallmoney field in the database for holding a house rent. The following is used to display the contents on the page<asp:Label ID="lblrent" runat="server" Text='<%# Bind("rent", "(0:0.00)") %>'></asp:Label>In development, the number is displayed correctly, with the decimal place, .e.g. 200.50 but on the live server the number is displayed as 20050,00. What I have noticed in the database is that the number is held differentlySQL 2005 - 200.5000SQL 2000 - 20050Is there a difference between SQL 2000 and 2005? How do I get around this problem?

View 6 Replies View Related

Displayed Date Format In SSRS?

Jan 29, 2008

My regional settings are set to the UK on the server and local machine. Does anyone know how to get SSRS displaying dates in DD/MM/YYYY format?

I have googled it, but just can not seem to find the answer.

View 9 Replies View Related

Subtracting Form Smalldatetime Stored In The Database.

Mar 19, 2007

I have a table that has two smalldatetime columns. like say a shift schedule.



Fname Lname Timein TimeOut Dept

What I need to find out is how to construct a SQL Query in BIDS so I can get a report containing names of people who have not worked in last 24 hours.

This query returns values but it seems it is not correct.

Select * FROM Table

WHERE (Timeout < { fn NOW() } - '23:59:59')

View 4 Replies View Related

DateTime Error In Stored Procedure

Apr 15, 2004

Hi;

I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??

Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)

ERROR:

Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

View 1 Replies View Related

Compare Datetime In Stored Procedure

Mar 14, 2008

Hi,

Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go











ALTER PROCEDURE [dbo].[UpdateActiveStates1]

@PROVIDERID INT

AS

DECLARE @STARTDATE DATETIME

DECLARE @ENDDATE DATETIME

DECLARE @ACTIVE BIT

SET @ACTIVE = 0

SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)

SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)

IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))

BEGIN

PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'

SET @ACTIVE=0

END

ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())

BEGIN

IF (@ENDDATE = NULL)

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'

SET @ACTIVE=1

END

ELSE IF (@ENDDATE >= GETDATE())

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'

SET @ACTIVE=1

END

ELSE

BEGIN

PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'

SET @ACTIVE=0

END

END

UPDATE Providers SET Active=@ACTIVE

WHERE ProviderID=@PROVIDERID

UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID







View 2 Replies View Related

Stored Procedure Excuting By Datetime

Jan 3, 2008

Hello,

I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.





Code Block

CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))

/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:

PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT

Please do anyone here know how I can achieve this please. Thanks.

View 6 Replies View Related

How To Pass DateTime To A Stored Procedure

May 16, 2008

When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".





Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();

SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs

if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}




I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').

View 3 Replies View Related

Format Date In A Stored Procedure

Nov 13, 2006

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 10 Replies View Related

Datetime W/ Format = D Still Showing Time Component Of Datetime

Jan 17, 2008

e.g.

1st March 2005 12:00:00

is showing as

01/03/2005 00:00:00

instead of

01/03/2005


Why does this happen?

View 4 Replies View Related

Date In String Format Has To Be Changed Datetime Format

Jun 15, 2005

I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
 

View 3 Replies View Related

Saving Datetime Data In A Stored Procedure

May 23, 2007

Hello everyone  I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value =  ????
 Thanks
 
 

View 3 Replies View Related

Passing Datetime Value Into A Class Stored Procedure

Jan 18, 2008

hello,
I have a stored procedure being called from my class.
All values are ok, except for the DateTime value. What am i doing wrong here ? Am wondering if its the date size of 50 I put there or what ?
command.Parameters.Add(new SqlParameter("@dob", SqlDbType.DateTime,50, dob1));
Error Message
Compiler ErrorMessage: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(string, System.Data.SqlDbType, int, string)' has some invalid arguments
thanks
Ehi

View 3 Replies View Related

Converting A String To Datetime In A Stored Procedure

Aug 19, 2004

I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.

But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.

Any idea how this is done?

View 1 Replies View Related

Sending Null DateTime Value To Stored Procedure From VB

Nov 3, 2005

In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)

The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,

However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.

I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.

I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?

Thanks,
Sarah

View 2 Replies View Related

Datetime Query Issue With C# Stored Procedure

Sep 9, 2007

Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:


SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition

C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();


Thank you,

View 2 Replies View Related

MS SQL Dynamic Stored Procedure Using A Datetime Variable

Feb 5, 2004

Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.

What I have is:


CREATE PROCEDURE busy_report

@TableName varchar(255),
@reporteddate datetime=NULL

AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"


-- Execute the SQL statement
EXEC(@SQLStatement)
GO

The error I keep getting is:

Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.

Any ideas appreciated.

(Edit:)

I've also tried it this way:


CREATE PROCEDURE UK_busy_report

@TableName varchar(255),
@reporteddate datetime=NULL

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN

/* Do some error processing */

PRINT 'Error Occured' END

ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"


-- Execute the SQL statement
EXEC(@SQLStatement)
GO

Which gives me the same error!

.logic.

View 5 Replies View Related

Optimizing Stored Procedure With Datetime Parameter

Nov 23, 2005

Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul

View 5 Replies View Related

DATETIME Conversion Problem In Stored Procedure

Nov 19, 2006

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

View 1 Replies View Related

Using Datetime Value In Insert Statement In Stored Procedure

Apr 26, 2008



I want to store datetime data in data table.

I am facing problem in using datetime data value in insert statement passed via stored procedure.
Any Help !Thanks in advance

View 3 Replies View Related

Change Data Format In Stored Procedure

Jul 31, 2006

Hi All,

My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...

regards,

Anas

View 1 Replies View Related

Strange Datetime Conversion Error In Stored Procedure.

Jul 23, 2005

Hi Everyone,I've been battling this for two days with no luck. I'm using SQLServer 2000.Here's the mystery: I've got a stored procedure that takes a singlevarchar parameter to determine how the result set is sorted. Here itis:CREATE PROCEDURE spDemo @SortField varchar(30)ASSELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDescFROM ActivityLogORDER BY CASE @SortFieldWHEN 'dtmTimeStamp' THEN dtmTimeStampWHEN 'strEmpName' THEN strEmpNameWHEN 'strOld' THEN strOldWHEN 'strNew' THEN strNewWHEN 'strActionDesc' THEN strActionDescENDGOWhen I execute the stored procedure in the Query Analyzer, it worksperfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or'strNew'. When passing in any of the other three possible values for@SortField, I get the following error:Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4Syntax error converting datetime from character string.Now instead of executing the stored procedure, if I copy and paste theSELECT statement directly into the Query Analyzer (after removing theCASE statement and manually trying each different value of @SortField),it works fine for all five possible values of SortField.Even though the error points to Line 4 of the stored procedure, itseems to me that the CASE statement is causing problems for some, butnot all, values of the @SortField parameter.Any ideas?Thanks,Jimmy

View 4 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related

Convert DateTime To A DateTime With Milliseconds Format

Nov 5, 2007

Hi,

I am trying to access a date column up to millisecond precession. So I cast date to as follows:



Code BlockCONVERT(varchar(23),CREATE_DATE,121)


I get millisecond part as a result of query but it€™s €œ000€?.

When I try to test the format by using getDate instead of DateTime column I get right milliseconds.





CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return

View 4 Replies View Related

How To Return Time && Number Format That Has Set In The Regional Setting Using Stored Procedure

Dec 11, 2007

How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Display Stored Procedure Output In Html Table Format

Mar 16, 2014

i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.

View 2 Replies View Related

Set A Variable To Datetime And Time To Exact Milliseconds In SQL Server In Stored Procedure AS A Single String

Nov 1, 2007

I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
 
Example:
set  MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
 
Please help, i tried  the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
 
This one doesnot display day correctly, it should show 01 but shows 1
 
 
 
 

View 2 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Jul 20, 2005

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 valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated

View 5 Replies View Related

SQL SERVER 2000: In Which Format The Datetime Will Be Stored In Sql Server 2000?

Feb 28, 2008

Hi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved