Dates for Criteria in a view

Question

Post reply

Dates for Criteria in a view

Mitch2007

SSC Eights!

Points: 830
More actions
November 16, 2011 at 3:42 pm

#247939

Hi all.
Dates again!
I am writing a view (SQL 2005 on a WinXP machine) which when I state the Dates as:
BETWEEN 20111016 AND 20111115,
works fine, however when I try to automate the view by coding the dates as:
BETWEEN CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), 112)
AND CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 14), 112)
It comes to a grinding halt.
Is there another way to specify the date which does not involve my changing the values in the view every month or getting the timeout message?
Once I get this fixed i'll be able to automate my process better.
Thanks in advance,
Mitch...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Peter Lavelle-397610 SSC Veteran Points: 215 More actions · Answer 1

Assuming that the column you are checking is a DATETIME then this should work faster:

BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15) AND DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 14)

Mitch2007 SSC Eights! Points: 830 More actions · Answer 2

Thanks Peter, unfortunatly it is not.

I have however discovered why it takes so long to run, and this is because it is running that code for EVERY record in the source table, not just for the records I want to return.

Tried to use variables for the dates but that method wont work in a view. I could run it from a SPROC but only if the sproc can be kicked off by Access from a user who does not have SQL server installed on their machine.

Any advice is greatly appreciated at this point!

Thanks again.

Peter Lavelle-397610 SSC Veteran Points: 215 More actions · Answer 3

Hi Mitch,

At this point we need more info.

Please provide:

Table definitions as CREATE TABLE statements.

Sample data as INSERT statements.

Any other relevant CREATE VIEW or CREATE PROCEDURE statements.

A description in business terms of what is supposed to happen along with expected output.

I believe Access can get its data from a SQL Pass-Through Query which EXECs a SQL Server stored proc, though I havent done this.

Peter

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Mitch2007 (11/16/2011)
BETWEEN 20111016 AND 20111115,
BETWEEN CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), 112)
AND CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 14), 112)
It comes to a grinding halt.

Ayup. Look at the conversions. My guess is there's an implicit conversion to VARCHAR() from the original structure which means any seeking went out the window. What's the datatype of the original column in SQL Server?

My guess is it's an int, which will lose to the varchar() formatting. If it's datetime, remove the varchar() bit entirely and leave it as a datetime format which is the result from the dateadd.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Revenant SSC-Forever Points: 42467 More actions · Answer 5

One way how to avoid the problems with BETWEEN when working with dates is to use what we call DateId. It is an integer (only 4 bytes!) with a number in format yyyymmdd. Today's DateId is 20111117.

This of course works only when you need only the date portion, but that works for most business tasks, and it also makes a practical Dim.

Mitch2007 SSC Eights! Points: 830 More actions · Answer 6

Thanks for the replies Gentlemen.

I decided to go with a Passthrough query created in VBA so that I could pass the Dates (which is stored on the source table as a NUMBER, not DATETIME) from values on the user form. This returns the desired Dataset and writes it to a local table so the User can then mess with it as they please.

Thanks again!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Mitch2007 (11/17/2011)
Thanks for the replies Gentlemen.
I decided to go with a Passthrough query created in VBA so that I could pass the Dates (which is stored on the source table as a NUMBER, not DATETIME) from values on the user form. This returns the desired Dataset and writes it to a local table so the User can then mess with it as they please.
Thanks again!

Alternatively, do one final cast/convert on the VARCHAR() to integer, and that would also help solve your problem.

However, pass-throughs are best under most circumstances.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

gofrancesc Hall of Fame Points: 3269 More actions · Answer 8

Hello,

what happens if you try to do the job inside Access?, you can construct your query in Access or you can code it and execute using VBA, maybe you don't need to create a view in SQL Server.

Maybe you try to create this view to avoid data manipulation from Access, you can create this view without the WHERE clause and add this clause in an Access query.

Francesc