.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Querying Datatime

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server



Can you please help me in writing this query

I have table with Dattime datatype with non cluster index.
Now I want to return time of  the values where data is not saturday



View Complete Post

More Related Resource Links

How to stored datatime into database - linq


Hi, DateTime dt = DateTime.Now; tbl_Payment tbl_Payments = new tbl_Payment() { UserId = UserID, PaymentType = DropDownList4.Text, AmountDeposit =10, Date = String.Format("{0:d/M/yyyy HH:mm}", dt); Compilation Error Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0029: Cannot implicitly convert type 'string' to 'System.DateTime?' Source Error: Line 683: PaymentType = DropDownList4.Text, Line 684: AmountDeposit =10, Line 685: Date = String.Format("{0:d/M/yyyy HH:mm}", dt), Line 686: Line 687: }; Using LINQ for data storage. In Table I column "Date" datetime. How to save a date in the table?


Querying a db for existence of a record.


Can someone suggest, what is the best approach using linq to sql to do the following

1. Check if a row exists in a table for a given criteria and...

2. If it exists return a particular field value else return '0'.

weird problem of using multithread to querying sql server

Hi,   I'm working on a project which needs query the sql server many times to pull the statistics info. the whole process is very slow. so, I'm trying to use the multithread to do the work. However, I noticed one weird thing:   In the program, I need pull, say 10000 documents from the sql to local PC, then can do some analysis. Each document size is different. If I used 1 thread, say for document A whose size is 5MB, it will take about 20 seconds for the SELECT query returns the result. Now, if I used 10 threads, I found that it will take much longer time for SQL to return the SELECT query for document A, say maybe 90 seconds. I got the run time for the SELECT from the sql profiler.   Overall, the performance of multithread is very similar to single thread even I use 50 threads. I used the same code for another project, the only difference is that that project scrapes data from website and this project gets data from sql. The other project works very well when using multithread. so, I'm kind of believe that the multithread part code works fine.    Could this because some settings on sql server? some bandwidth limitation? any clues?   any help/suggestion will be appreciated.   thanks reader      

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

XML data type in SQLSERVER 2000 for querying

Hello,   we’ve developed an application that uses a database with a XML data type column. It works fine with SQLSERVER 2005 an above, but we cannot create the structure in SQLSERVER 2000 because it doesn’t support that data type.   We’ve made some test with SQLXML 3.0 packet and SQLSERVER 2000 but it only adds pseudo-support to work with XML documents. We need to store XML documents into a column and then make selects using XPath sentences over that column. For instance: select * from users where description.exist('//info[@value = "francesc"]') = 1 Is there any way to achieve this using SQLSERVER 2000?? Thanks in advance! Francesc

Timeout when Querying a SharePoint list

Hello, I am querying a SharePoint 2007 list which contains 7000 folders and 25000 items. My application always query a minor subset of this data. (we don't query large data sets). but still I get timeouts in the webservice call to extract the elements. Can you please give me some guidance on how to resolve this timeout issue? My list may be big, but I am querying only small volume of data.

Problems querying XML data with null values

If the tags have "xsi:nil" in them, then my query failes with a "XML parsing: line [x], character [x], undeclared prefix" error.  So my question is how to get the query to handle or ignore those tags with "xsi:nil" embedded in them.  So specifically, the first SQL query below is successful, and the second one fails with the aforementioned error.  Any comments appreciated, thanks. -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </data>'; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('data/customer') AS T(customer); Returns: 1 Allied Industries 2 Trades International ================== -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> <fax xsi:nil="true" /> </customer> <customer> <id>2</id> <name>Trades International</name> <fax xsi:nil=&quo

Querying a dataset filled from xml files

I am writing an application in vb.net 2008 to extract multiple xml files selected from a destined folder. App has managed to select a folder, then it reads each xml file one by one inset the data in dataset. After the app inserts xml into dataset I want to establish relationship among tables created in the data set. For more info xml has this strucure: <RedLionHeder> <Customer> <Site> <Meter1> <Meter2>......<Metern> <Channel1> <Channel2>.....<Channeln> So I want to establish relationship as Each file will have 1 customer, 1 or many sites, each site will have more than 1 meters and each meters will have more than 1 channels. If any one help me in this I would appreciate. Please not the xml files are of the size of 80 to 90Kb therefore it contains lot of data. Here is the code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        'Open the Folder Browser Dialog         'Dim dsOutput As New Data.DataSet        Dim fb As New FolderBrowserDialog        Dim count As Integer        With fb             .ShowDialo

Querying, getting the results and re-using the results on a different datasource and dataset. Is it

Hello all,  I have a dataset that is running a very simple query, pulling an OrderID field based on the TransactionID parameter (User Input).  This dataset is pointed to Database_A. SELECT OrderID FROM Orders WITH (NOLOCK) WHERE OrderID = @TransactionID   Now, this query above might return one of more records. I need to query another system (Database_B) using the results from this query. I need to store the OrderIDs that I got somewhere and check them against another system and display the results on my report. Please note - Database_B is located on a totally different server.  Can you guys think of a way of doing this? I'm using SSRS 2008, not 2008 R2.

Error querying SSAS cube.....permission problem???

I've been developing a BI solution with SQL/SSRS/SSAS 2005.  I've done all the development with an account that has full admin permissions on the server.  Now I am trying to give users access to the data without admin permissions.  When a user runs an SSRS report that queries an SSAS cube I get the following error:   An error has occurred during report processing. (rsProcessingAborted)  Query execution failed for data set 'Division'. (rsErrorExecutingCommand). Since I don't get this message when I run the report I'm thinking it's a permission thing.  I set up a domain user group, 'SQL Users', and gave them access to the SQL Server Reports database and the Report Manager but don't know if I have to grant permissions on the SSAS database.  SSAS doesn't use the same security settings that SQL uses.  I added SQL Users as a role in my database but that didn't help. I'm not sure what else to try...

Question on querying for averages


I have a table with a 'status' one is always 'In Delivery' and its followed up by another status such as 'Rejected' or 'Delivered'

I need to find the average time it takes for each 'In delivery' status to be updated as rejected or delivered by the LDPID

I wasn't sure if I should use rank or avg any ideas?


We have a table with the following schema -

CREATE TABLE [dbo].[Delivery](
    [DeliveryId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [LeadId] [bigint] NOT NULL,
    [LDPId] [bigint] NOT NULL,
    [DeliveryStatus] [nchar](25) NOT NULL,
    [DeliveryDate] [datetime] NOT NULL
    [LeadDeliveryLogId] ASC



Here is an example of the data in the table -

deliveryid   leadid           lpdid    deliverystatus                deliverydat

Inserting and querying data on the same table


I need to implement a logic where in I insert or update a table in a loop.If the record is inserted then I need to update the table.To check this I have given IF NOT EXISTS statement with a query. But IF NOT EXISTS always returns true, so many records are getting inserted and update always fails

How do I resove this problem

Querying InfoPath forms deployed to a Sharepoint Library


Hello all,

We have a form library that stores a form with information for each database we host for our clients. Each database we host has an associated form with information such as:

Database Owner
Application Supported
Backup Location
Security Requirements
And so on...

I was asked to put the form together back a couple months back so we could inventory our databases.

It works, the library now has over 30 forms.

Now I'm being asked to transfer these records from the infopath forms/libray into a database table so we can report from.

I have no clue if I can connect to these forms and pull data from them, do you guys know?

I would like to loop through this library and pull the data from each form into a SQL table, is this doable?

Let me know if you guys can help.

Best regards,
-Igor Santos

Querying indexing catalog causes error on the word "weight"



On our web server we have a search form that fires off multiple searches and combines the results into 1 page.  The main search is of the indexing catalog for our web site.  We have used this for several years and it works just fine.  The company just sponsored a weight loss program and we have discovered that the word "weight" returns an error.  The word "weigh" works fine but if we add that "t" on the end it fails.  Here is the error:

Incorrect syntax near 'weight'. Expected '!', '(', NOT, ISABOUT, FORMSOF, STRING, PREFIX_STRING. SQLSTATE=42000

This is the query at the time of execution:

Select DocTitle,URL from SCOPE() where (CONTAINS(CONTENTS, 'weight') OR CONTAINS(DocTITLE, 'weight')) order by Rank Desc

Does anyone have an idea of how to get past this problem?  I'm guessing that "weight" is probably a key word or something similar.

Querying XML Data


I'm pretty new to XML within SQL Server, but have picked up quite a bit. Now comes the hard part - querying.


Here's the format of my XML column:


Code Block


<EventType>ALTER_TABLE</< FONT>EventType>

<PostTime>2007-10-15T10:13:13.417</< FONT>PostTime>



Querying all pages in a SharePoint site collection

I have a requirement to create an A-Z index of all pages within a SharePoint site collection.  This includes pages that are in subsites and subsites of subsites!!  Any ideas on how I shoudl approach this??

Querying LDAP, domino server problem



perhaps someone could help me. I have the following code which works fine when using Active directory as LDAP:

DirectorySearcher searcher = new DirectorySearcher(new DirectoryEntry(path, user, password, type);

searcher.Filter = "(&(objectclass=person)(name=" + name + "));

SearchResult result = searcher.FindOne();

if (result != null) {

  DirectoryEntry user = new DirectoryEntry(result.Path, user, password);

  var properties = user.Properties["memberOf"];


When trying to query a domino LDAP the line:

  var properties = user.Properties["memberOf"];

causes the following exception: System.DirectoryServices.DirectoryServicesCOMException (0x80072021): A protocol error occurred.

So my question is, can I handle a domino server with DirectoryEntry and DirectorySearcher? If yes, how?

Is there an alternative to query users from a domino server LDAP?


Thank you!



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend