Resolution Datetime And GroupName
Sep 25, 2005
Hi guys,
I am trying to add data to 2 columns CaseResolvDatetime and CaseResolvGroupName based on the criteria:
1. for a CallID, the last datetime where Resolution='Resolved'
2. where there is no 'Resolved', the 2nd last datetime & groupname for a particular CallID
-----------------------------------------------
DECLARE @table TABLE(CallID CHAR(8), ResolvDatetime DATETIME, GroupName VARCHAR(15), Resolution VARCHAR(15), CaseResolvDatetime datetime, CaseResolvGroupName varchar (15))
INSERT @table(CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName)
SELECT '00458176','08/1/2005 1:41:54 PM','Field Sup CoOrd','Resolved',Null,Null UNION ALL
SELECT '00458176','08/9/2005 3:35:24 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/12/2005 9:54:37 AM','BSSAppSvcs-Club','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/16/2005 10:32:40 AM','CDR SysOps','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 11:18:30 AM','CDR Server','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 2:13:15 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/24/2005 4:01:24 PM','CDR Server','Resolved',Null,Null UNION ALL
SELECT '00458176','08/25/2005 4:29:44 PM','Field Sup CoOrd','Reassigned',Null,Null UNION ALL
SELECT '00458176','08/26/2005 2:59:19 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00458176','09/9/2005 10:42:44 AM','Field Sup - Vic','Completed',Null,Null UNION ALL
SELECT '00461166','09/7/2005 9:49:02 AM','BSSAppSvcs-Apps','Resolved',Null,Null UNION ALL
SELECT '00461166','09/7/2005 10:26:42 AM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00461166','09/9/2005 10:19:13 AM','BSSAppSvcs-Apps','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:00 PM','Service Desk','Reassigned',Null,Null UNION ALL
SELECT '00461166','09/15/2005 1:15:11 PM','Service Desk','Resolved',Null,Null UNION ALL
SELECT '00456633','08/08/2005 1:18:32 PM','BSSAppSvcs-Club','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 2:02:07 PM','Service Desk','Completed',Null,Null UNION ALL
SELECT '00456633','08/26/2005 3:31:36 PM','Service Desk','Completed',Null,Null
SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table
UPDATE @table
SET CaseResolvDatetime =
CASE WHEN Resolution = 'Completed' AND CallId = (select CallId from @table where GroupName like 'Ser%' AND Resolution = 'Completed') then ResolvDatetime
ELSE NULL
END
SELECT CallID, ResolvDatetime, GroupName, Resolution, CaseResolvDatetime, CaseResolvGroupName FROM @table
-------------------------------------------------
At the moment I get the error:
Server: Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Yet when I change the line:
"where GroupName like 'Ser%'" to "where GroupName like 'CDR%'"
... I get no error.
View 2 Replies
ADVERTISEMENT
Jun 8, 2006
I set up a group within an SQL Server database, of which I am a member. In some visual basic code, I do a dislay of user names.
My question: why does the code show my individual name (Servermyname) as opposed to showing the group name I want to see? I have confirmed that the only user of the database is the group, and not an individual user name.
Any ideas? Thanks!
*********************************************************************
This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.
View 3 Replies
View Related
Jul 20, 2005
Hi all,I want to create a stored procedure which will extract the "GroupName"from the record returned by "sp_helpuser". In order to do this I needto execute "sp_helpuser" which returns the entire record. I want tojust extract the "GroupName" from the record and return it to myapplication. How do I go about this?Thanks in advance,Alvin
View 4 Replies
View Related
Dec 20, 1999
In 7.0, an application development change has been made to defer name checking in
stored procedures until exection time. This allows a clean store of the proc in the system catalog
regardless of objects existing or not. At execution time, the proc is compiled, and object resolution is
done. Problem - the procedure fails if object names, column names, etc are wrong or don't exist.
So, during nightly cycle's the procedures bomb out.
Is there anyway to disable this 'deferred name resolution', or am I at the mercy of the developers?
Can I make the resolution immediate?
Thanks!
Dean
View 1 Replies
View Related
Aug 26, 2006
Friends
I have one SQL server2000 which have Mixed Mode authentication.It is working fine in PC which server instance is situating.But I have another PC which having connectvity tools of SQL Server.When I am trying to access SQL Server from the second PC through SQL authentication access getting denied.In the same time if I am accessing any of first PC's Folder using it's administrator password.Then if try with SQL authentication I am able to connect with SQL Server in the First PC from second PC .Please note SQL server instance is not in windows authentication mode .What may be the problem.Please suggest me a resolution
Thanks in Advance
Filson
View 1 Replies
View Related
Oct 4, 2001
I would like to be able to turn off the deferred name resolution feature in SQL2000 when compiling stored procedures. Is this possible?
Sidney
View 1 Replies
View Related
Feb 12, 2007
I am merging two databases and created two different schemas to contain the objects in the new DB. I would like the objects in both schemas to be visible to one user but I can't figure out how to make this happen. I thought I could just set the Owner of the schemas to the same User but that didn't work. At the moment, the User default schema is set to dbo. I would like the default to be both schemas but this isn't an option.
Is there any way to have multiple schema objects visible to a single user? Thanks!
View 4 Replies
View Related
Jun 27, 2006
Hello,
I'm running into problems with my replication where I get the following error:
Violation of PRIMARY KEY constraint
I know what the error means, and I know what is causing it. In my case, a property is being added to an inventory item independently at the publisher and subscriber end. Each available property has a particular ID, each inventory item has its own unique ID, and of course the properties per inventory item are stored in a linking table. I am using UUIDs for the inventory items, to avoid collisions in that aspect, but the list of properties is fixed (currently only 15 - 20 available properties), so it doesn't make sense to me to have managed ranges, UUIDs, or other such things for the properties. Of course, I could apply a "source ID" to each added property to avoid these collisions, but I'd prefer not having to redesign the database, not to mention deal with the extraneous copies of properties.
My preference would be to simply have the server delete the copy on the server and take the subscriber row. I would have thought that using the "subscriber always wins" conflict resolver would have this effect, but it doesn't work for me. Is there a straightforward way of dealing with this problem? Am I missing something obvious? I've looked into a custom conflict resolver, but that seems like overkill for what must be a fairly common scenario.
For the record, the publisher in my case is SQL Server 2005, and the subscribers are SQL Server Mobile clients.
Any advice would be greatly appreciated!
Thanks,
Adrien.
View 3 Replies
View Related
Jul 6, 2015
I am using Allwayson to replicate a FCI to a remote site. (windows 2012R2) Although all is implemented, iam getting a strange error with the listener resources after ive made a failover test.
It keeps saying "Name resolution not yet available"from my understanding this is trying to update an entry that doesn't exist (since the user has permissions to do it).
View 4 Replies
View Related
Mar 16, 2004
Is there any way to set up name resolution for DTC when there are two servers with the same name? I have three servers - two production systems named "SQL" and an archive system "DMART". I want to run DTC procedures to archive data from both SQL's to the DMART. Is there any way to set up the DMART so it can properly resolve the names from each of the SQL's? They are on separate networks, but unfortunately have the same NetBIOS name.
View 3 Replies
View Related
Jun 11, 2015
I'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
View 2 Replies
View Related
Oct 28, 2007
I posted a question a few days ago, to get some general information what this command does:
command.cmd
osql -S (local)SQLEXPRESS -E -i DropDB.sql
DropDB.sql
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Caching')
DROP DATABASE [Caching]
I have gotten some great answers. Now I like to ask a deeper question. This command was created for SQL Server Express, for an internal software package at my company, by an outside vendor. However, sometimes this command doesn't work 100% on all user computers. So I have two questions.
1. Is there any way to either enhance the command, or the database command, to provide error diagnostics, to find out the issue for an individual user computer, why it doesn't always work?
2. Can anyone think of any scenarios that would prevent this from working?
The key element here is that this caching database is supposed to allow individuals to use a particular piece of software, when they aren't connected to the network at work. But it doesn't always work on each computer - 100% of the time - even when the computer has SQL Server Express installed, and is running Windows XP.
Randy
View 2 Replies
View Related
Apr 29, 2008
Hello,
is it possible to use CROSS APPLY like this:
SELECT [x]
FROM [Order] CROSS APPLY (SELECT .OrderNo + 3) [x]
i know you need [x].* but is it possible to use a function so that only [x] is needed?
thx in advance for help
MattGo
View 3 Replies
View Related
Jul 10, 2006
I have a couple of tables set up with column level tracking and using the "Subscriber Always Wins" conflict resolver.
I am still trying to work out why there is a conflict as the only updates to these rows happen at the subscriber.
What I am finding is that a conflict occurs and there appears to be a delay between the conflict being recognised and then resolved.
The reason I know there is a delay is because this is playing havoc with a data extract that relies on the updates from the subscriber.
For instance, I have a job on which the status is changed. That status change triggers an extract via BizTalk to another system. BizTalk, via an SQL adapter extracts the data from the server and I find that some of it is missing in the extract.
I go and look into the database at the raw data and find that the data is in fact there.
I have certain triggers running which set up export flags in the database to say this data should be exported. This works perfectly when there are no conflicts.
In each case where data has not been extracted, a conflict occurs.
Is there any way to guarantee that the conflict will be resolved at the same time as the synchronisation is complete - i.e. within the same transaction?
I thought replication was supposed to be a single transaction - you get it all or you get nothing.
I'm interested to hear from anyone who has experienced this behaviour.
Thanks
View 2 Replies
View Related
Oct 22, 2006
Hi all,
I
am doing a work were i have to syncronize data between a Publiser
(PC, SQL SERVER 2005), and a subscriber (Pocket PC, SQL CE MOBILE). I
studied lots of articles and i managed to put the syncronization to
work, the problem is in conflict resolution!
The subscriber can´t insert or delet, only update!!
For exmple: I have a table that contais the quantity of a product X,
for example 100 units, this goes to Pocket, but later arrive move 100
units that are increased in the PC, so we get 200 in the PC, (but 100
in Pocket), no problem if i syncronize now (Pocket will have 200 too),
but if remove in Pocket for example 50 units, i've changed the same
column in both Publisher and Subscriber database, if i syncronize now,
i'll have a conflict, the final result should be 150 of product X in
both databases (100 + 100 - 50), but the Publisher wins the conflict
and the final result is 200!
I never worked with stored procedures and in microsoft theres and article called How to: Implement a Stored Procedure-Based Custom Conflict Resolver for a Merge Article (Replication Transact-SQL Programming), but they dont explain it very well and i dont know how to do my own stored procedure and implement it to the merge article.
in my own stored procedure i'll have to do some calculations to get the
result i want, maybe using some table for the additions and
subtractions. (Does the com based Addition resolver do this for me???)
Maybe you have experienced some problem like this, and could help me!
How should i do the stored procedure?
Oh, i tried to use de addiction resolver and the average resolver
of com based but nothing happed, dont know why, i read some stuff and
i think the addition could solve my problem, but it doesn´t work, i read above what you said and installed service
pack 1 for sql 2005, but i instaled and nothing
happened!
When i change to the resolver that the subscriver wins, it works, why addition dont work?
Thanx
View 3 Replies
View Related
Jun 29, 2007
Hi!!
I created a report in SSRS which incorporates an image.And I have written a code using C# to export the report to PDF. While I am exporting this report to PDF from the SSRS, the resolution of the image got decreased.
Can anyone give me a solution to configure the resolution of the image in the PDF.
Thanks in advance.
View 6 Replies
View Related
Oct 29, 2007
An outside vendor created this command and SQL files for internal
software, using SQL Server Express, which is installed on every user
machine
osql -S (local)SQLEXPRESS -E -i DropDB.sql
The SQL file has this:
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Caching')
DROP DATABASE [Caching]
This command was created for SQL Server Express, for an internal software package at my company, by an outside vendor. However, sometimes this command doesn't work 100% on all user computers. So I have two questions.
1. Is there any way to either enhance the command, or the database command, to provide error diagnostics, to find out the issue for an individual user computer, why it doesn't always work?
2. Can anyone think of any scenarios that would prevent this from working?
The key element here is that this caching database is supposed to
allow individuals to use a particular piece of software, when they aren't connected to the network at work. But it doesn't always work on each computer - 100% of the time - even when the computer has SQL Server Express installed, and is running Windows XP.
Randy
View 2 Replies
View Related
May 31, 2006
I'm having some problems getting Interactive Conflict Resolution working under SQL Server 2000 SP4. I have set "Allow Subscribers to resolve conflicts interactively" under the Resolver tab for the article in the publication. I have set "Enable synchronization using Windows Synchronization Manager" and "Resolve conflicts interactively" under the Synchronization tab for the subscription. I have set "Resolve conflicts interactively" under the Other tab for the subscription properties in the Windows Synchronization Manager. However, when I generate conflicting data in the publishing and subscribed DBs and synchronize using the manager, the resolution is automatic and a warning pops up informing me that "conflicts occurred while syncronizing the data and were resolved according to defined rules." Am I missing anything here?
View 5 Replies
View Related
Aug 8, 2007
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
View 4 Replies
View Related
Aug 24, 2006
Hello,
executing the first script listed below resulted in the error message (translation):
Message 7890, Level 16, Status 1, Line 1
Error when registrating Endpoint. Potential port conflict ...
use AdventureWorks;
go
CREATE ENDPOINT SQLEP_AWPersons
STATE = STARTED
AS HTTP
(
PATH = '/AWpersons',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'PersonInfo'
(NAME='AdventureWorks.dbo.uspGetPersonInfo'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Persons'
)
I changed the port in the script to 8084:
PORTS = (8084),
Error Message 102: Wrong Syntac near ')'.
I changed the port in the script to '8084':
Error Message 7888: The stated IPv6-Address is not supported. The server is potentially not configured for the IPv6 Connectivity or the address is not formated in an known IPv6-Format.
Could you help?
Best regards,
Wolfgang
View 3 Replies
View Related
Feb 18, 2008
Hello, I want to get data from datatable as below. I am getting error:
BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments. I did not understand what is wrong. Because everything is same as msdn library.
my codebehind is:
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClientPartial Class Default2 Inherits System.Web.UI.Page
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=Database;Initial Catalog=otel;Integrated Security=True;Pooling=False"
End FunctionProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim connectionString As String = _
GetConnectionString()
' Create a SqlConnection to the database.Using connection As SqlConnection = New SqlConnection(connectionString)
' Create a SqlDataAdapter for the Suppliers table.Dim mailsAdapter As SqlDataAdapter = _ New SqlDataAdapter()
' A table mapping names the DataTable.mailsAdapter.TableMappings.Add("Table", "Pages")
connection.Open()Dim PagesCommand As SqlCommand = New SqlCommand( _"SELECT * FROM Pages", _
connection)
PagesCommand.CommandType = CommandType.Text
' Set the SqlDataAdapter's SelectCommand.
mailsAdapter.SelectCommand = PagesCommand
' Fill the DataSet.
Dim dataSet1 As Dataset = New Dataset("Pages") 'ERROR MESSAGE HERE...........................................mailsAdapter.Fill(dataSet1)
connection.Close()LblPageName.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(1))
TxtPageTitle.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(2))TxtPageSummary.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(3))
Rte1.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(4))TxtPageimgUrl.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(5))
End Using
End Sub
End Class
View 1 Replies
View Related
Feb 26, 2008
Hi SQL Server Team,
In order to add a data source by using ODBC Administrator, programmatically i am using SQLConfigDataSource. Thus calling SQLConfigDataSource with the fOption set to either ODBC_ADD_DSN for creating a new DSN.
Following is the stattement specified in the source code. where type_of_driver = "Microsoft Access Driver (*.mdb)"; and parameters contains the data source source credentials information such as DSN, UID, PSW, FIL, Description, DataDirectory,DEFAULTDIR and DBQ all separated by " ".
rc = SQLConfigDataSource(NULL, ODBC_ADD_DSN, type_of_driver, parameters);
after execution of below mentioned command an error is encountered i.e -1 is returned if the DataDirectory path exceeds certain nested directories link. for eg. the file path specified below.
C:My DocumentsClients CurrentAHW Netcare BenefitsDeliverables1-5 Results Chain - system LevelAlberta NetcareBenefits Logic System Level V01.rcm
Creating a datasource with the file name i.e Benefits Logic System Level V01 every time.
rc = :: SQLConnect(hDbc, (unsigned char *)szSource, SQL_NTS, (unsigned char *)szUserName, SQL_NTS, (unsigned char *)szPassword, SQL_NTS);
Also following error message was encountered during execution of SQLConnect command.
/*szErrorMsg = 0x0012e834 "[Microsoft][ODBC Microsoft Access Driver]General error Not enough information to connect to this DSN with SQLConnect. Use SQLDriverConnect."*/
Thus followed the instruction smentioned in the error message an dtried using SQL DriverConnect() in the following manner.
rc = :: SQLDriverConnect(hDbc, NULL,(SQLCHAR *)"DSN=RCM",SQL_NTS,outstr, sizeof(outstr),
&outstrlen,SQL_DRIVER_NOPROMPT);
Still an error is encountered but this time it returns a RETCODE of 100 whrease earlier it had returned -1.
Inform the procedure to be followed using SQLConnect when DataDirectory path is too long.
An early reply will be appreciated.
Cheers & Regards,
Saresh
View 1 Replies
View Related
Jul 17, 2014
We have a winforms application with local rdlc reports. Â On Surface Windows 8 Pro devices only, reports render such that they are scaled down and don't fill the page. Â If resolution is lowered on the device, the report scales up to fill the page.
 This affects the appearance in print layout and well as the actual printed size.
If we export to pdf, it renders to fill the page (as expected). Â If you print to pdf or to printer, the scale is shrunken.
If we change the resolution settings so that "Make  text and other items larger or smaller" is set to small, the issue is resolved except the tablet is not very usable on this setting.Is this a bug?
View 10 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Dec 6, 2006
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
View 1 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "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."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
May 13, 2006
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest
View 2 Replies
View Related
Mar 11, 2014
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
View 1 Replies
View Related
Sep 7, 2007
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies
View Related
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
Jun 15, 2004
I have the following SQL:
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
View 6 Replies
View Related
Jul 20, 2005
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -
View 1 Replies
View Related
