MongoDB Query to find record using greater $gt or less than $lt operators and then sort

MongoDB Query to find record using greater $gt or less than $lt operators and then sort

Query
db.grades.find({"score":{$gt:65}},{"score":1,student_id:2,}).sort({"score:1})

grades is the collection.

MS SQL Server Query to get Date difference of two dates in Days hours minutes and seconds

Hi in this post i will show how to get the date difference of two dates in DD:HH:MM:SS:MS format in MS Sql Server.

DD: Days
HH: Hours
MM: Minutes
SS: Seconds
MS: Milli Seconds

Sql Query :

SELECT *
,Days = datediff(dd, 0, DateDif)
,Hours = datepart(hour, DateDif)
,Minutes = datepart(minute, DateDif)
,Seconds = datepart(second, DateDif)
,MS = datepart(ms, DateDif)
FROM (
SELECT DateDif = EndDate - StartDate
,a.*
FROM (
SELECT StartDate = convert(DATETIME, '05-02-2013 01:10:00.000')
,EndDate = getdate()
) a
) Result

Output:

using where condition like sql in .net | Linq query to fetch rows based on where condition

HI lets see how to fetch records in LINQ based on the where condition specified.

Example:

var query = from r in dtSampleDataTable.AsEnumerable()

                        where r.Field<string>("Salary") == "1000"

                        select r;

            if (query.Count() > 0)

            {

                DataTable dt1 = query.CopyToDataTable(); //fetching filtered rows to a new datatable.

                object sumSalary = dt1.Compute("Sum(Salary)", "");

            }

string s = sumSalary.ToString();

LINQ startswith,EndsWith example | Asp.net LINQ Query to get or fetch rows from a datatable for a coloumn starting with any letter,word,Number

Hi in this post i will show using LINQ query how to fetch rows from a dataTable for a column starting or ending with any letter,word,Number etc.

Example:

1. here i will fetch rows from a datatable for Names Ending with 'ar'

var query = dtSampleDatatable.AsEnumerable()
                 .Where(row => row.Field<string>("Name").EndsWith("ar"));

            DataTable dtSampleDatatableNew = new DataTable();
            dtSampleDatatableNew = query.CopyToDataTable(); // Adding filtered rows to a new datatable.

2. To fetch Names starting with 'ar'

var query = dtSampleDatatable.AsEnumerable()
                 .Where(row => row.Field<string>("Name").StartsWith("ar"));

            DataTable dtSampleDatatableNew = new DataTable();
            dtSampleDatatableNew = query.CopyToDataTable(); // Adding filtered rows to a new datatable.

[Resolved]The XML parse error near XML text | Msg 6602 Procedure sp_xml_preparedocument, Line 1 The error description is 'Whitespace is not allowed at this location.' | Msg 8179 Could not find prepared statement with handle 0.

hi in this post i show how to resolve the error generated while XML Parsing in SQL.

Example :

DECLARE @INDEX INT
DECLARE @XMLEMPLOYEE NVARCHAR(MAX)

SET @XMLEMPLOYEE = N'<ROOT><EMPLOYEE EMPID="1" EMPNAME="CHANDAN"/>
                            <EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/> 
                     </ROOT>'

EXEC SP_XML_PREPAREDOCUMENT @INDEX OUTPUT
 ,@XMLEMPLOYEE

SELECT EMPID
 ,EMPNAME
FROM OPENXML(@INDEX, 'ROOT/EMPLOYEE') WITH (
  EMPID NVARCHAR(50)
  ,EMPNAME NVARCHAR(550)
  )

After Executing this above query, we get the error as below:

The XML parse error 0xc00ce513 occurred on line number 2, near the XML text "<EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/>".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Whitespace is not allowed at this location.'.
Msg 8179, Level 16, State 5, Line 9
Could not find prepared statement with handle 0.


The problem is due to the using of ampersand(&) in the xml document.

Solution:

While creating the XML document replace the ampersand(&) with &amp; and then re-run the query to get the correct output.

DECLARE @INDEX INT
DECLARE @XMLEMPLOYEE NVARCHAR(MAX)

SET @XMLEMPLOYEE = N'<ROOT><EMPLOYEE EMPID="1" EMPNAME="CHANDAN"/>
                            <EMPLOYEE EMPID="2" EMPNAME="CHANDAN &amp; SINGH"/> 
                     </ROOT>'

EXEC SP_XML_PREPAREDOCUMENT @INDEX OUTPUT
 ,@XMLEMPLOYEE

SELECT EMPID
 ,EMPNAME
FROM OPENXML(@INDEX, 'ROOT/EMPLOYEE') WITH (
  EMPID NVARCHAR(50)
  ,EMPNAME NVARCHAR(550)
  )

It will give the ouput as :

EMPID EMPNAME
1 CHANDAN
2 CHANDAN & SINGH

Check Data-length of the all the data in the column in Sql Server | query to check size of all the data present in the column

Taking an Example , Suppose we want to check the data-size of all the data present in a column for a Employee table :

Query :

SELECT DATALENGTH(CUSTOMER_ID) AS EMP_ID_LENGTH ,
       DATALENGTH(EMPLOYEE_NAME) AS EMPLOYEE_LENGTH,
       *
FROM EMPLOYEE_TABLE

changing column size in sql | Query to increase or decrease column length in sql server

Changing Column Size :

Suppose you want to change the size of a column having datatype as NVARCHAR of length 100.
We will increase it length to 200.

Query :

ALTER TABLE CUSTOMER_TABLE
ALTER COLUMN CUSTOMERNAME nvarchar(200)

update table using from clause in a database | using from clause updating table records query example

Below is the query using from clause to update table records :

update Employee 
set Deptname = cc.Deptname
FROM Employee dd
inner join Department cc on  cc.DeptId = dd.DeptId
where dd.salary is not null