Select based on Date- How to?

Question

Post reply

Select based on Date- How to?

Viewing 7 posts - 16 through 21 (of 21 total)

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

mpdillon Hall of Fame Points: 3566 More actions · Answer 1

I understand. No functions on the left hand side of the operator.

If I want to reutrn the records Less than or equal to My Date then it will be

Where EndDt < "My date + 1 day"

For all the records from a single day, I would use

Where EndDt >= "My date" and EndDt < "My date + 1 day"

This is because SQL evaluates the inequality faster than an equality that uses a String function.

Lynn,

This all started because I wanted to delete all vacations which ended prior to today when the user logged into my program. This minimizes the data stored in their files and does not clutter their screen with out dated information.

I incorrectly wrote

Where Convert(varchar(10),EndDt,101) < Format(Now, "MM/dd/yyyy")

This could have been accomplished correctly by

Where Cast(Convert(varchar(10),EndDt,101) as smalldatetime) < Format(Now, "MM/dd/yyyy")

Or

Where Convert(varchar(10),EndDt,112) < Format(Now, "yyyyMMdd")

However, these are inefficient. To select all the records prior to today the correct way would be

Where EndDt < Format(Now, "MM/dd/yyyy")

If instead I was looking to return records from today only.

Where EndDt >= Format(Now, "MM/dd/yyyy") and EndDt < Format(DateAdd(d,1,Now),"MM/dd/yyyy")

And lastly if I were looking for all record less than and including today

Where EndDt < Format(DateAdd(d,1,Now),"MM/dd/yyyy")

Epilogue - The handling of dates has been difficult for me with SQL server because I had spent 12+years working with an ERP system that evolved from Btrieve to Pervasive to MS SQL. The date fields were always stored as YYYYMMDD. Time was rarely stored and when it was stored, it was stored in a separate field. With the YYYYMMDD format, finding a specific day or using an inequality was very straight forward. Adding the time data to the same field has taken some getting used to on my part.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

That's a pretty good and correct summary (heh... except for the format function). And, yes, you do understand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mpdillon Hall of Fame Points: 3566 More actions · Answer 3

It was late. The DateAdd function is incorrect. It should read.

DateAdd("d",1,Now)

or in VB 2008

DateAdd(DateInterval.Day,1,now)

T-Sql to the left of the operator, VB to the right.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

mpdillon (11/20/2008)
It was late. The DateAdd function is incorrect. It should read.
DateAdd("d",1,Now)
or in VB 2008
DateAdd(DateInterval.Day,1,now)
T-Sql to the left of the operator, VB to the right.

Nope... should be DateAdd(d,1,GETDATE()) in T-SQL. GETDATE() also has some synonyms you can use, but you don't need the quotes around the "d".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pg53 Ten Centuries Points: 1303 More actions · Answer 5

mp - As a bit of reassurance - your Epilogue is spot-on. You're not the only person to find the lack of time-only and date-only data types in SQL Server a bit counter-intuitive

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

Heh... then there are the folks that believe those datatypes are nothing more than a form of formatting that should really be done in the GUI. I'm one of them 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

For the most part, Jeff, I agree. However, the new TIME and DATE data types in SQL Server 2008 will make it easier to build date and time dimensions for a data warehouse.