SQL Date Range Problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Date Range Problem

Author

Topic

jemes
Starting Member

6 Posts

Posted - 2009-07-15 : 07:49:48

I'm having a problems accessing specific dates using a ms sql database.

Anything before Jan 15th will not display using excel and asp etc but as soon as I alter the t-sql statement to after those dates the data is displayed.

All the data for pre Jan 15th is stored in the database and we can query it using ms sql studio.

Does anyone have an ideas or know how to go about solving this problem?

Thanks

Jemes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 07:51:55

Let me guess. Your dates are not stored with the DATETIME datatype and you are using VARCHAR to store dates?

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 07:53:15

let's see the sql statement, the table definition and maybe a few sample rows

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 07:53:42

quote:
Originally posted by Peso

Let me guess. Your dates are not stored with the DATETIME datatype and you are using VARCHAR to store dates?

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

probably a good guess

jemes
Starting Member

6 Posts

Posted - 2009-07-15 : 08:15:04

Thanks for your reply's.

We did'nt acutally set up the db, it's a system we have bought and have had for several years but has just suddenly stopped working. So I would assume the datatype would be set correctly as we have never had any problems in the past.

The data is in the database and can be seen using an sql statement using ms sql studio but when I use the same statement using a seperate system we created using asp or excel I get no data back or errors.

Thanks

Jemes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 08:23:54

how 'bout showing us the sql statement and table definition. then we can try to help

jemes
Starting Member

6 Posts

Posted - 2009-07-15 : 08:26:12

The t-sql statement im using is below...

But like I say until a few weeks ago this was working.


SELECT     TOP (100) PERCENT dbo.course_main.course_id
FROM         bb_bb60.dbo.gateway_categories FULL OUTER JOIN
                      bb_bb60.dbo.gateway_course_categories ON 
                      bb_bb60.dbo.gateway_categories.pk1 = bb_bb60.dbo.gateway_course_categories.gatewaycat_pk1 FULL OUTER JOIN
                      dbo.activity_accumulator ON bb_bb60.dbo.gateway_course_categories.crsmain_pk1 = dbo.activity_accumulator.course_pk1 FULL OUTER JOIN
                      dbo.course_main ON dbo.activity_accumulator.course_pk1 = dbo.course_main.pk1
WHERE     (dbo.activity_accumulator.timestamp > CONVERT(DATETIME, '2008/12/01 00:00:00', 102)) AND 
                      (dbo.activity_accumulator.timestamp < CONVERT(DATETIME, '2008/12/25 00:00:00', 102))
GROUP BY dbo.course_main.course_id, bb_bb60.dbo.gateway_course_categories.gatewaycat_pk1
HAVING      (dbo.course_main.course_id IS NOT NULL) AND (bb_bb60.dbo.gateway_course_categories.gatewaycat_pk1 = '59')

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 08:36:24

what is the data type of dbo.activity_accumulator.timestamp ?

jemes
Starting Member

6 Posts

Posted - 2009-07-15 : 08:37:34

quote:
Originally posted by russell

what is the data type of dbo.activity_accumulator.timestamp ?

The datatype is set to datetime and the values in that would be something like 30/06/2008 16:05:27

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 08:54:06

[code]
WHERE (dbo.activity_accumulator.timestamp > CONVERT(DATETIME, '2008/12/01 00:00:00', 102)) AND
(dbo.activity_accumulator.timestamp < CONVERT(DATETIME, '2008/12/25 00:00:00', 102))
[/code]

this line in your code is choosing a date that is specifically between 2008/12/01 and 2008/12/25..

you could rewrite this with a between statement,

[code]
WHERE dbo.activity_accumulator.timestamp between '2008/12/01 00:00:00' and '2008/12/25 00:00:00'
[/code]

can you post the results of a query that works and one that doesn't

thanks

---
Eric Robinson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 09:12:18

WHERE dbo.activity_accumulator.timestamp >= '20081201 -- yyyymmd format
AND dbo.activity_accumulator.timestamp < '20081225' -- yyyymmdd format

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

jemes
Starting Member

6 Posts

Posted - 2009-07-15 : 09:18:50

Changed the statement but still no results.

Seems strange that as soon as I enter 2009 dates the data displays but the 2008 data is in the db.

Just wondering if the problem could be on the server with a service maybe? Just seems odd that the sql statement used to work and does with dates from 6 months ago but not now.

jemes
Starting Member

6 Posts

Posted - 2009-07-16 : 04:30:33

We have noticed a pattern that we seems to be able to retrieve data from 6 months ago. Yesterday we could retrieve timestamp data from the 16th Jan 2009 but now thats blank and the 17th Jan 2009 data is available up to the present day?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 04:33:33

Do you have a clean-up job that deletes records older than 6 months?

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 20:01:22

quote:
Originally posted by Peso

Do you have a clean-up job that deletes records older than 6 months?

... or, perhaps, an archive system where the older rows are available through a partioned view?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources