Problem with Dates in SQL 2000

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

SQL Server Development (2000)

Problem with Dates in SQL 2000

Author

Topic

lfmn
Posting Yak Master

141 Posts

Posted - 2003-08-07 : 13:03:50

We recently migrated from SQL Server 7 to SQL Server 2000. We are now having a problem with dates.

example:

We create a stored procedure

create procedure dbo.proc_daily

@EndDate datetime
as

SET NOCOUNT ON

declare @begdate datetime

select top 1 @begdate = wk_beg from tbl_week3 (nolock) where wk_end = @EndDate

Select * from sometable where date between @begdate and @enddate

This query returns no data. If you hardcode the dates in, it does return data. Also, if we make the following change (changing the variable within the stored procedure, it also returns data.

create procedure dbo.proc_daily

@EndDate2 datetime
as

SET NOCOUNT ON

declare @Enddate datetime
set @Enddate = @EndDate2

declare @begdate datetime

select top 1 @begdate = wk_beg from tbl_week3 (nolock) where wk_end = @EndDate

Select * from sometable where date between @begdate and @enddate

If anyone has any clue to what might be causing this problem, we are desperate!

SQL is useful if you don't know cursors :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 13:14:48

That seems very strange, maybe a bug although I've never encountered it. Could you post the DDL for your table and some sample data (INSERT statements is best if you want quick help)?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 13:18:47

Try recreating the SP.
Just moving the date from one field to another shouldn't change it.

Also try displaying the dates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lfmn
Posting Yak Master

141 Posts

Posted - 2003-08-07 : 13:31:58

nr:

Tried all of that.

We've been working on it all day and have tried many different solutions. We've come up with a work around, but I'm looking to see if anyone else has encountered the same problem.

I posted another question regarding how to output the data type. Even though I am declaring @enddate a datetime data type, it would be nice to be able to output the data type to see if it is still a datetime datatype after it is passed into the stored procedure.

SQL is useful if you don't know cursors :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 13:35:24

Ummm, a data type will not change for a variable unless you re-declare it with a different data type.

If you could post your DDL and DML for your table, we could test it out on our machines to see if we can find any problems.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 13:36:20

If you have declared a datetime variable then it won't change.
The most likely problem is that the passing of the datetime into the Sp is failing - due to date formatting maybe.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-07 : 13:43:46

quote:

select top 1 @begdate = wk_beg from tbl_week3 (nolock) where wk_end = @EndDate

Why do you have a "top 1" without an ORDER BY ??

- Jeff

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 13:51:22

And why use (nolock)? --{swallowing aspirin now}--

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 13:58:20

make mine a double absolute rocks, twist..

quote:

NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

Brett

8-)

SELECT POST=NewId()

lfmn
Posting Yak Master

141 Posts

Posted - 2003-08-07 : 15:00:13

The Top 1 is poor coding. There can be only one end date for each beginning date. Some of our legacy code is less than perfect.

< If you have declared a datetime variable then it won't change.
The most likely problem is that the passing of the datetime into the Sp is failing - due to date formatting maybe.>

The date is being passed. We did a print statement and an ISDATE() to make sure that is was being passed as a date. I know that a variable SHOULDN'T change, but I have to suspect everything until it's ruled out.

We use nolock for performance gains. The way our data is updated makes dirty reads OK.

I'll try to post the DDL and some sample data tomorrow.

I appreciate everyones input.

SQL is useful if you don't know cursors :-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 16:08:42

quote:
We use nolock for performance gains. The way our data is updated makes dirty reads OK.

Please don't say this - you'll give other developers a misleading message.

(nolock) is, almost always, a bad idea. Rarely is a database contentious enough that (nolock) is required. More often, it's used to allow application developers to run "reporting queries" from transactional databases, which is another bad idea.

The performance gains are minimal, approaching nil in fact when applications are properly designed with very short transactions. The reason why you experience gains is because you're telling SQL Server not to perform required checks on data integrity and transactional isolation. The perils of possibly serving up bad data (even if at one point in time it was ok) ... I have seen wreckage that took weeks to fix.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-07 : 20:27:29

quote:
Rarely is a database contentious enough that (nolock) is required. More often, it's used to allow application developers to run "reporting queries" from transactional databases, which is another bad idea.

Sorry Jonathan, it's not quite that cut and dried. I've dealt with plenty of databases that WERE contentious enough, and NOLOCK made a huge difference. The difference between running and not running at all.

And it's all well and good to say that developers should not report off of a production server, but if that's the only server you have, then that's that.

quote:
(nolock) is, almost always, a bad idea

True, it's ALMOST always a bad idea, maybe 98% of the time, but that last 2% happens more often than you think.

lfmn
Posting Yak Master

141 Posts

Posted - 2003-08-08 : 08:13:48

<Please don't say this - you'll give other developers a misleading message.> setbasedisthetruepath

When you get a little more experience you'll realize that nolock can be useful in some situations. It's also totally irrelevant to this topic. I've been writing SQL code for more than seven years and I've worked on two different systems with more than a terrabyte of data. I know when to use nolock and when not to.

Also, with all due respect, it would be nice to stay on the subject. The question is "has anyone had problems with dates after migrating from SQL Server 7 to SQL Server 2000".

SQL is useful if you don't know cursors :-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-08 : 12:32:06

quote:
True, it's ALMOST always a bad idea, maybe 98% of the time, but that last 2% happens more often than you think.

You've got a funny way of agreeing with someone

lfmn - believe me, I'm not attacking you. Experience isn't the issue here, just differing conclusions. I too have dealt with SQL Server for years and years using terabyte-sized data sets. So what.

My message, simply, is that it isn't a fair or complete representation of (nolock) to claim it boosts performance without talking about the potential negatives. After all, if there were no negatives, SQL Server would use it by default.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 12:48:23

quote:
Originally posted by robvolk

quote:
Rarely is a database contentious enough that (nolock) is required. More often, it's used to allow application developers to run "reporting queries" from transactional databases, which is another bad idea.
Sorry Jonathan, it's not quite that cut and dried. I've dealt with plenty of databases that WERE contentious enough, and NOLOCK made a huge difference. The difference between running and not running at all.

And it's all well and good to say that developers should not report off of a production server, but if that's the only server you have, then that's that.
quote:
(nolock) is, almost always, a bad idea
True, it's ALMOST always a bad idea, maybe 98% of the time, but that last 2% happens more often than you think.

Yeah but Rob, what version? Saw a lot of that in 6.5, because you had too....

and I guess the point is, are people still just coding because they've done it that way in the past? Are there any contention/ locking problems now? Wasn't 6.5 page level only (can't remeber)...

and with proper indexing, how is:

quote:

select top 1 @begdate = wk_beg from tbl_week3 (nolock) where wk_end = @EndDate

Select * from sometable where date between @begdate and @enddate

going to run in to difficulty...terabyte or not...

BOL even discourages it

And hey...

quote:

When you get a little more experience you'll realize

chill

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 12:52:26

quote:

Also, with all due respect, it would be nice to stay on the subject. The question is "has anyone had problems with dates after migrating from SQL Server 7 to SQL Server 2000".

True...but we need more to go on..

DDL, sample data....

Brett

8-)

SELECT POST=NewId()

lfmn
Posting Yak Master

141 Posts

Posted - 2003-09-02 : 07:41:50

quote:
Originally posted by X002548

quote:

Also, with all due respect, it would be nice to stay on the subject. The question is "has anyone had problems with dates after migrating from SQL Server 7 to SQL Server 2000".

True...but we need more to go on..

DDL, sample data....

Brett

8-)

SELECT POST=NewId()

Actually, you don't. This is a yes or no problem. If you haven't had the problem, then there is no need to answer the post.

Thanks to all that responded. It turns out that the problem was solved with an upgrade to service pack 3.

SQL is useful if you don't know cursors :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources