Select
akrashdi
I'm trying to get records for today and for few hours from tomorrow date, I'm
trying something as following which makes my application halted
SELECT * FROM accounts, jobs
WHERE (jobStatus != 'unconfirmed')
AND (jobs.accountId=accounts.accountId)
AND (jobDate BETWEEN '2010-08-22' AND '2010-08-24')
OR (jobDate='2010-08-25' AND jobTime < '04:00:00')
Any idea how to achieve this...appreciated.
Tom
You need to tell us how your dates are stored in the database, are the
CHAR, VARCHAR, DATETIME, SMALLDATETIME. It makes a difference.
Most likely your dates are stored in a DATETIME column and you should
use something like this.
DECLARE @Start DATETIME;
DECLARE @Finish DATETIME;
SELECT @Start = '2010-08-22 00:00:00.000'
,@Finish = '2010-08-24 04:00:00.000';
SELECT *
FROM accounts, jobs
WHERE jobDate BETWEEN @Start AND @Finish;
akrashdi via SQLMonster.com
and jobTime as TIME. Its actually Apache Derby database.
Tom wrote:
>> Hi,
>>
>[quoted text clipped - 8 lines]
>>
>> Any idea how to achieve this...appreciated.
>
>You need to tell us how your dates are stored in the database, are the
>CHAR, VARCHAR, DATETIME, SMALLDATETIME. It makes a difference.
>
>Most likely your dates are stored in a DATETIME column and you should
>use something like this.
>
>DECLARE @Start DATETIME;
>DECLARE @Finish DATETIME;
>SELECT @Start = '2010-08-22 00:00:00.000'
> ,@Finish = '2010-08-24 04:00:00.000';
>SELECT *
>FROM accounts, jobs
>WHERE jobDate BETWEEN @Start AND @Finish;
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201008/1
Erland Sommarskog
> Actually, Date and Time are stored in two different columns: jobDate as
> DATE and jobTime as TIME. Its actually Apache Derby database.
Apache Derby tells me nothing, but I assume it uses SQL 2008.
What indexes are there on the table? It would probably be best to create
a computed column with both date and time and index that column. But if
this is a vendor product, it may not be a supported.
I seem to recall that I posted one suggestion the other day, but I may
have overlooked that you had two columns. This is an alternative:
SELECT * FROM accounts, jobs
WHERE (jobStatus != 'unconfirmed')
AND (jobs.accountId=accounts.accountId)
AND (jobDate BETWEEN '2010-08-22' AND '2010-08-25')
AND NOT (jobDate='2010-08-25' AND jobTime >='04:00:00')
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Iain Sharp
>
>SELECT * FROM accounts, jobs
>WHERE (jobStatus != 'unconfirmed')
>AND (jobs.accountId=accounts.accountId)
>AND (jobDate BETWEEN '2010-08-22' AND '2010-08-24')
>OR (jobDate='2010-08-25' AND jobTime < '04:00:00')
Should probably be.
SELECT * FROM accounts, jobs
WHERE (jobStatus != 'unconfirmed')
AND (jobs.accountId=accounts.accountId)
AND (
(jobDate BETWEEN '2010-08-22' AND '2010-08-24')
OR (jobDate='2010-08-25' AND jobTime < '04:00:00')
)
Your way gets you all unconfirmed jobs with their matching accounts if
they are between 2010-08-22 and 2010-8-24
and a cross join of all accounts and all jobs dated 2010-08-25 where
jobtime < 04:00.
Better yet woul dbe to code the join explicitly.
SELECT * FROM
accounts inner join jobs on jobs.accountId=accounts.accountId
WHERE (jobStatus != 'unconfirmed')
AND (
(jobDate BETWEEN '2010-08-22' AND '2010-08-24')
OR (jobDate='2010-08-25' AND jobTime < '04:00:00')
)
You still need the extra brackets, or you get all jobs on the 25th
before 04:00 regardless of status.
Iain