DATETIME parameter - 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)

DATETIME parameter

Author

Topic

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 15:21:08

Hello,

I have a query I was given to run periodically. It has two DATETIME variables to be plugged in before running. The first time I ran it I used the parameters in this form: '2008-02-01' and got the appropriate results. The next time I ran it I got an empty result set until I used the form '2009-02-24 08:06:30.000'. I have no idea what might have changed. Can anyone enlighten me?

M_M

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 15:55:07

show us the query and sample data. it depends how you are using the datetime variable.
> @Datetime, < @Datetime, >= > @Datetime

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 16:09:45

Thanks for taking the time to look at this. I tried doing away with the variables with the same results. This gets me nothing:

WHERE
appDatetime BETWEEN '2009-02-24' AND '2009-03-04'

And this gets me what I want:

WHERE
appDatetime BETWEEN '2009-02-24 08:14:04.000' AND '2009-03-04 09:36:47.000'

As I mentioned origianlly, the first WHERE clause USED to work.

M_M

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 16:28:29

try this.
WHERE
appDatetime >= DATEADD(dd,DATEDIFF(dd,0, '2009-02-24'),0)
AND appDatetime <= DATEADD(dd,DATEDIFF(dd,0, '2009-03-04'),0)

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 16:48:34

Nope. Same results. I did try your version of the BETWEEN:

appDatetime >= '2009-02-28'
AND
appDatetime <= '2009-03-04'

Which didn't work either. But if I include the full datetime:

appDatetime >= '2009-02-28 19:06:45.000'
AND
appDatetime <= '2009-03-04 09:36:47.000'

Now it works. The major puzzle to me is why it used to work.

M_M

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 17:06:32

ok what datatype if your appDatetime ? also can you show us actual data of appDatetime ?

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 17:24:22

The table definition shows it as a 'datetime_entry' which is listed in the User Defined Datatypes as datetime_entry( datetime, NOT NULL).

A partial result set for the query

SELECT DISTINCT
appDatetime
...

is:

2009-03-04 09:36:47.000
2009-03-04 09:03:09.000
2009-02-28 19:06:45.000
2009-02-24 08:39:08.000
2009-02-24 08:14:04.000

M_M

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 17:32:06

ok gotcha try this. i believe it will work
WHERE
DATEADD(dd,DATEDIFF(dd,0, appDatetime),0) >= DATEADD(dd,DATEDIFF(dd,0, '2009-02-24'),0)
AND DATEADD(dd,DATEDIFF(dd,0, appDatetime),0) <= DATEADD(dd,DATEDIFF(dd,0, '2009-03-04'),0)
remember '2009-03-04' is equal to '2009-03-04 00:00:00.000'

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 17:46:03

Yes, that seems to have done the trick! And this is a helpful bit of info:

quote:
remember '2009-03-04' is equal to '2009-03-04 00:00:00.000'

I'll play around and see if I can't figure out why it stopped working originally, but at least I can run it as it was meant to be run going forward.

Thanks so much for your time and for sharing your expertise!

M_M

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-04 : 17:49:50

quote:
Originally posted by mmesford

Thanks for taking the time to look at this. I tried doing away with the variables with the same results. This gets me nothing:

WHERE
appDatetime BETWEEN '2009-02-24' AND '2009-03-04'

Why this condition were not returning any record while table has a record of data 2009-02-28?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 17:51:16

Maybe post your whole query.

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 17:55:48

Yes, that's the remaining mystery. It works if the parameter is:

'2009-02-24 08:14:04.000'

And it works if I use the code provided by yosiasz.

M_M

mmesford
Starting Member

7 Posts

Posted - 2009-03-04 : 18:10:21

Oh crap. I figured it out and it's all my stupidity. The key was yosiasz's point:

quote:
remember '2009-03-04' is equal to '2009-03-04 00:00:00.000'

As it turns out, all the data in the query as a whole has a datetime greater than '2009-03-04 00:00:00.000' because it all took place ON THAT DAY. I tried stripping the query down to a single SELECT stmt to post at sodeep's request. At that point it returned a few records that had been filtered out by the rest of the query. So, it all works if I use '2009-03-5'. My apologies for taking up your time. I still struggle (obviously) with datetime issues.

M_M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources