November 19, 2008 at 9:36 pm
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.
November 19, 2008 at 10:19 pm
That's a pretty good and correct summary (heh... except for the format function). And, yes, you do understand.
--Jeff Moden
November 20, 2008 at 5:12 am
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.
November 20, 2008 at 8:03 pm
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
November 21, 2008 at 4:43 am
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
November 21, 2008 at 5:19 am
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
November 21, 2008 at 6:49 am
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.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply