Problem with Sql query havign date and time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Sql query havign date and time

Author

Topic

jai2808
Starting Member

27 Posts

Posted - 2008-12-05 : 00:24:06

Hi,
I have a table with following date
RecordDate Dated
1 30/09/2008 07:02
2 30/09/2008 07:03
3 30/09/2008 07:03
4 30/09/2008 07:03
5 30/09/2008 07:03
6 30/09/2008 07:03
7 30/09/2008 07:06
8 30/09/2008 07:06
9 30/09/2008 07:06
10 30/09/2008 07:06
But when i use the below query its not returing any rows
SELECT *
FROM Ordered_Info
WHERE (Dated= '30-Sep-2008')

SELECT *
FROM Ordered_Info
WHERE (Dared= '09/30/2008')

Can some one please let me know where am i going wrong.

SimpleSQL
Yak Posting Veteran

85 Posts

Posted - 2008-12-05 : 00:53:03

Can you send schema of the table. output of sp_help order_info.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-12-05 : 01:50:44

U Can Try This....
Like ur date format in table,,,,

Select * from Tablename Where Fieldname = 'dd/MM/yyyy'

This works only when if the values in your table stored in this format...'dd/MM/yyy'

Defaulty it set to 'mm/dd/yyyy'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 02:51:40

SELECT *
FROM Ordered_Info
WHERE Dated>= '30-Sep-2008'
AND Dated < '1-Oct-2008'

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-05 : 06:05:37

quote:
Originally posted by ashishashish

U Can Try This....
Like ur date format in table,,,,

Select * from Tablename Where Fieldname = 'dd/MM/yyyy'

This works only when if the values in your table stored in this format...'dd/MM/yyy'

Defaulty it set to 'mm/dd/yyyy'

It wont work too if you use

Select * from Tablename Where Fieldname = 'MM/dd/yyyy'

See Peso's reply

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 06:35:20

Hi All,

Peso -- you suggested

quote:

SELECT *
FROM Ordered_Info
WHERE Dated>= '30-Sep-2008'
AND Dated < '1-Oct-2008'

To get all values between 2008-Sep-30 @ 00:00:00 and before 2008-Oct-01 @ 00:00:00

Is that any better or worse than:


SELECT * FROM ordered_info WHERE DATEDIFF(DAY, [dated], '2008-Sep-30') = 0

Which is the syntax I usually use?

-- Also jai2808, you should be aware that this method of identifying dates is not an ISO standard (in different locales the Months have different names!)

The 2 ISO standards are

Short Form : 'YYYYMMDD' (example '20080503' 2008-May-03)
and.
Long Form : 'yyyy-mm-ddTHH:MM:SS.MS' (example '2008-05-03T12:00:01.000' 2008-May-03 @ 1200 (and 1 second))

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 06:38:45

Better.
If you use DATEDIFF(DAY, [dated], '2008-Sep-30') it is not very likely the query optimizer can utilize any index.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 06:39:25

Excellent. Thanks for the tip!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 06:43:43

See this example. I have TallyDates table with over 2 million dates.

First query is

SELECT	*
FROM	TallyDates
WHERE	Date >= '20081205'
	AND Date < '20081206'

and second query is

SELECT	*
FROM	TallyDates
WHERE	DATEDIFF(DAY, Date, '20081205') = 0

They both return with same result but they make different use of the database resources.

First query plan uses a CLUSTERED INDEX SEEK and need only 3 reads from the database to find the result.
Second query uses a CLUSTERED INDEX SCAN and need 4223 reads from the database to find the result.

First query need only 1 ms at the database to get the result.
Second query 400 ms (with parallellism) and 600 ms (without parallellism) to get the result.

The conclusion is to NEVER EVER make calculations over an indexed column if you want to use the index.
And that is because the index has a fixed value and not the new, calculated value, stored.

This is one often recurring performance trouble shooter.
Last week I changed the WHERE clause of a query because it was really slow.

The query returns with 240,000 records and needed 11,000,000 reads from the database and took 58 seconds to complete.
After rewrite (changed WHERE clause so not longer a calculation is made over indexed column) the query now still return same 240,000 records but only need 80,000 reads and completes in 7 seconds.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-05 : 06:52:30

Your point is well made sir!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources