Question about Sql query

Question

0.00/5 (No votes)

See more:

Having problem that my Sql Query is "

SQL

SELECT a.in_ts,a.jobCode,b.name 'CompanyName',a.jobName,a.contactPerson,a.cpDesignation,a.location,a.jobType,a.monthlySalary,a.qualification,a.experience,a.remarks FROM tg_jobMaster a, tg_companyMaster b WHERE a.companyCode=b.companyCode AND convert(varchar(10),a.in_ts,101) BETWEEN '12/30/2012' AND '03/07/2013' AND isAllocated is NULL AND isExpired is NULL ORDER BY a.jobCode DESC

".This query is not working but When i am selecting fromdate 02/30/2012 this query is working.
Plz solve the problem.

Posted 6-Mar-13 18:15pm

Nalini Ranjan Sahoo

Add a Solution

Comments

Shanalal Kasim 7-Mar-13 0:33am

Please provide table structure with sample data

__TR__ 7-Mar-13 0:49am

What do you mean by "The query is not working". Are you getting any error message or its just the query is not returning any rows?

Edit: If you are getting an error its because 02/30/2012 is february 30th which is an invalid date.

Davidduraisamy 7-Mar-13 1:14am

have you got the result?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Khorshed Alam, Dhaka · Answer 1 · 2013-03-06T18:51:00

Try this one:

SQL

SELECT a.in_ts,
       a.jobCode,
       b.name 'CompanyName',
       a.jobName,
       a.contactPerson,
       a.cpDesignation,
       a.location,
       a.jobType,
       a.monthlySalary,
       a.qualification,
       a.experience,
       a.remarks
FROM   tg_jobMaster         a,
       tg_companyMaster     b
WHERE  a.companyCode = b.companyCode
       AND CONVERT(DATETIME,a.in_ts, 101) BETWEEN CONVERT(DATETIME,'12/30/2012',101) AND CONVERT(DATETIME,'03/07/2013',101)
       AND isAllocated IS NULL
       AND isExpired IS NULL
ORDER BY
       a.jobCode DESC

__TR__ · Answer 2 · 2013-03-06T19:14:00

try the below approach

SQL

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromDate = '12/30/2012'
SET @ToDate = '03/07/2013'

SELECT a.in_ts,
a.jobCode,
b.name 'CompanyName',
a.jobName,
a.contactPerson,
a.cpDesignation,
a.location,
a.jobType,
a.monthlySalary,
a.qualification,
a.experience,
a.remarks 
FROM tg_jobMaster a, tg_companyMaster b 
WHERE a.companyCode=b.companyCode 
AND DATEDIFF(DAY,a.in_ts,@Todate) >= 0 AND DATEDIFF(DAY,a.in_ts,@FromDate) <= 0
AND isAllocated is NULL 
AND isExpired is NULL 
ORDER BY a.jobCode DESC

Davidduraisamy · Answer 3 · 2013-03-06T19:18:00

Solution 3

Use this condition:

convert(varchar(10),ExpDate,112) between convert(varchar(10),'20121230' ,112) and convert(varchar(10),'20130123' ,112)

Posted 6-Mar-13 19:18pm

Davidduraisamy

Question about Sql query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0