select * from ledgertrans lt
WHERE lt.TRANSDATE BETWEEN '1 Jun 2015 00:00:00'
AND '30 Jun 2015 23:59:59.999' order by lt.transdate desc
The above query looks okay to me - the only thing which I am not understanding is why would it return the data for 1st July 2015. - when I clearly specified that it should be between 1 Jun - 30 Jun.
Any guidance?
If TransDate is a datetime then the last time for a given day is 23:59:59.997 so 23:59:59.999 rounds to 00:00:00.000 on the next day.
To avoid rounding problems with date types it is better to write queries like:
WHERE lt.TRANSDATE >= '20150601'
AND lt.TRANSDATE < '20150701'Thank you for such a wonderful explanation - big time thanks - sincerely!
Remember to click @Ifor 's heart icon
The milliseconds will be .997 for datetime - but if the column is changed to datetime2 it will have a higher precision. In that case - this recommendation will still work. However, using the between and setting it to .997 would no longer work.
First , read any book on SQL. The only date FORMAT allowed in ANSI/ISO Standard SQL (and a ton of other ISO things!) is ISO-8601 (https://xkcd.com/1179/). Not knowing this and working in IT is like an engineer who never heard of the Metric System.
Secondly, please post DDL; this is minimal Netiquette.
CREATE TABLE Ledgertrans
( ..
trans_date DATE NOT NULL,
..);
Because you have the correct data type, the BETWEEN works just fine.
trans_date BETWEEN '2015-07-01' AND '2015-07-30'
While so true T-SQL, this will also optimize better than the temporal computations,
I'll take your work for it that is what the standard says ... but it is terrible advice to give for SQL Server because whilst it might work OK for DATE it is not reliable for a DATETIME, and users who ask questions on forums are likely to apply the same advice to DATE, DATETIME and DATETIME2
On SQL Server users would be better advised to use 'yyyymmdd' for date constants, as that is treated by SQL Server unambiguously.
SET NOCOUNT ON
SET LANGUAGE 'English'
GO
SELECT CONVERT(datetime, '20150730')
GO
SELECT CONVERT(datetime, '2015-07-30')
GO
SET LANGUAGE 'French'
GO
SELECT CONVERT(datetime, '20150730')
GO
SELECT CONVERT(datetime, '2015-07-30')
GO
gives this:
Changed language setting to us_english.
------------------------------------------------------
2015-07-30 00:00:00.000
------------------------------------------------------
2015-07-30 00:00:00.000
Le paramètre de langue est passé à Français.
------------------------------------------------------
2015-07-30 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
Have you played with the new DATE, TIME, and DATETIME2(n) data types? They default to the ISO-8601 "yyyy-mm-dd" display format because Microsoft is aligning itself with the ANSI/ISO standards. It will be awhile before the old Sybase CONVERT() options are deprecated like the old *=, original BIT data type, ISNULL() versus COALESCE() and other Sybase legacy features (aka "family curse") were, but you can smell the coffee now.
The professionals who work with more than one SQL in their shop will do it now, and the "hillbilly programmers" will stick to dialect out of comfort. Hell, I got funny looks for putting infixed OUTER JOIN code in comments when I was consulting. But when my clients upgraded, they called and asked me about it, then un-commented the code and tested it. It worked and ported to their DB2 database too! I should have double billed them 
I think that, sadly, that just makes the problem worse though. People posting on this forum will quite likely use DATETIME, and if they are advised to use 'yyyy-mm-dd' string constants and then they assign to a DATETIME (i.e. using the same date constant style) they will get no error message provided that they are using US-English locale (which I would guess is 99% of testing environments) and thus will be harbouring a dangerous bug.
Hence I think that recommending 'yyyymmdd' is a lot safer for the target audience here.
As this is a SQL Server specific forum, we get almost no one here who is using multiple SQL platforms. That reinforces my thinking that folk are better served by being given advice that is "safe" in deference to "standards compliant".
But ... that said ... you raise an interesting point about moving to new standards compliance. I have no idea how that will happen in the Real World though 
"*=" has all but disappeared ... which I suppose proves that it is possible to "change", but the code base using "*=" (in SQL Server, probably NOT also in Oracle!!) was small. The current code base in MS SQL Server is massive, compared to the time when "*=" was commonplace, and thus any change will be very hard (i.e. "costly") to implement
I think Microsoft could do more in this regard - whether via a VERBOSE / STRICT mode that "moaned" about anything not standards compliant, or a more robust approach - e.g. Microsoft could disallow DATETIME in all new installs (by default) and provide an option to enable it with a database-specific setting.
Then Newbies would only use DATE / TIME / DATETIME2 and at least we would not get any new code using DATETIME
I don't know why but when DATE & TIME were added I thought of them as "Long overdue"!! rather than "Standards Compliant" and thus I also thought that the String Constant for DATE being 'yyyy-mm-dd' was an annoyance because of the risk of assignment [in the same, flawed, format] to a DATETIME object.
My organisation is probably?? typical; we are not embracing DATETIME2 because it needs more bytes of storage, and we don't need the greater time accuracy; we have not yet considered splitting our existing DATETIME columns into a pair of DATE & TIME columns because of the huge body of code that would need changing. Maybe we should? 
DATE (i.e. excluding the TIME part) tests would be easier, and SARGable 
but we have a huge investment in code that uses/expects DATETIME - my guess is that there are many other organisations in a similar situation.
If anyone has a Magic Bullet please let me know
Look at your old DATETIME and see how much of it is spent chopping off the time to "00:00:00.000"; I loved to CAST( CEILING(CAST(foo_timestamp AS FLOAT)) AS DATETIME) because I had 16-bit minicomputers with floating point hardware!
But now it is worth doing a text search for this so you can replace the old code with DATE in DDL and get rid of whatever kludge you used. And over time, everyone had a favorite kludge, so the nothing was consistent.
Get in the habit of doing it the right way for all conditions and BETWEEN isn't it. BETWEEN requires a Closed/Closed temporal range and that is true only for the very low resolution DATE and DATETIME2(0) datatypes. Since behind the scenes, BETWEEN resolves to a >=/<= operation and someone that gets religious and changes the very low resolution DATE datatype column to a finer temporal resolution to include any time element, you should always use the Closed/Open method of defining a temporal range in the form of ...
WHERE SomeTemporalColumn >= StartDateTime
AND SomeTemporalColumn < FirstDateTimeYouDontWant
@Ifor did this one correctly as it will handle any and all temporal datatype resolutions for days without needing to be changed if the underlying datatype is changed.
As for posting the DDL, it truly wasn't necessary in this case so put a conditional clause in your canned answer. 
As for the ANSI/ISO thing, that's ok only if you don't really want to do anything complex or you actually believe in the myth of true code portability. 
And using DATE just so you can use BETWEEN is a kludge in itself. 
And using DATE just so you can use BETWEEN is a kludge in itself. <<
No, it is not. You use DATE because it is the correct unit of measurement for this data model. In commercial apps this the expected case; even lawyer do not bill to the nanosecond. Again the resources wasted by clipping the old Sybase clock ticks do not just destroy performance because of indexing, but they screw up the data model.
Understood on the lawyer thing but they do frequently bill to the nearest 15 minutes. And no one is talking about clipping old Sybase clock ticks. Well, maybe you are because you use the DATE datatype to do such clipping. 
Rather that doing it two different ways just to accommodate wholes days or days with time, I'll always recommend in favor of the proper Closed/Open temporal range rather than even having to worry about whether the range can be satisfied by BETWEEN or not, which actually resolves to >=/<= behind the scenes.anyway.
The use of BETWEEN isn't bullet proof to someone changing the resolution of whatever datatype is used whereas the Closed/Open method is and always will be.
Actually, I start with the DATE data type in the DDL now. I use only 3 byes for each date, have no clipping to do and get much easier code. Seriously, how much code and performance have we wasted in T-SQL trimming off the time to '00:00:00.000' to fake a day with a point in time? 
You need to read how INTERVAL temporal data works in SQL. We have one and only one way to accommodate whole days or days with time. This is why we can add a TIME to a DATE to get a DATETIME2(n) ; {datatypes - How to combine date and time to datetime2 in SQL Server? - Database Administrators Stack Exchange}
The use of BETWEEN isn't bullet proof to someone changing the resolution of whatever datatype is used whereas the Closed/Open method is and always will be. <<
I see your point, but I and Chris Date, argue that in a strongly typed language like SQL,the precision is a new data type. From a less theory oriented view of the world, your front end has to change the screens and paper forms.
Understood but the problem with most of that is that not everything is time-insensitive. I agree that if you need to work only with whole dates then the DATE or DATETIME2 datatypes are the way to go (although using the Closed/Open method is also still the way to go to keep it bullet proof especially since it costs nothing extra in resources other than just several bytes of storage in a proc). The unfortunate part is when people just do that as a matter of rote and then they fill the forums with how to add TIME to DATE so that they can calculate (for example) duration between a start and end date/time.
And, there's no wasted performance if you use the Closed/Open method of stipulating temporal criteria instead of using BETWEEN. You should read up on it so that you know that BETWEEN resolves to a similarly formed Closed/Closed formula behind the scenes and takes exactly the same time and resource usage.
Speaking of duration and in the absence of any sane function to do so in SQL Server, MS and the rest of the world did a great disservice to us all by making it so that the likes of DATETIME2 could not be used to do even simple date math directly. Instead, people are left to doing insane conversions to milliseconds (incredibly limited range) or seconds or even minutes. For most calculations (thinking of your lawyer billing example), taking a difference in seconds or even minutes is close enough but it's still a calculation that could be done much easier if direct date math were allowed as it is with the DATETIME datatype.
Swinging the gate the other way to something with a much more substantial impact to code, the steps to add days and times to a date/time using DATE and TIME or DATETIME2 would be comical if it weren't for the incredible number of steps people have to take especially if sub-second accuracy is necessary. Using the DATETIME data-type and if accuracy to 3.3 milliseconds is tolerable, then it boils down to a single simple addition of the two DATETIMEs. Even "lowly" spreadsheets can do this! Why not DATETIME2?
As for the link you provided, there can be only one reason why the OP wants to add '23:59:59.9999999' to a date and that's to use BETWEEN for temporal criteria instead of the Closed/Open method. The worst part of that post is that out of all the people that responded (and there are some supposed heavy hitters that responded and they actually did the worst), all of them missed that implied nuance. If your intention was to show the waste and improper thinking when it comes to intervals, then you're spot on. The real key is as I said though. There was no critical thinking as to what the real problem is and that is that the OP probably wants to use BETWEEN for temporal criteria and that's the worst thing that can be done.
As for reading about INTERVALs, I have (and I find that most of it is unnecessarily complex and can easily be greatly simplified) but apparently the people who crippled DATE, TIME, and DATETIME2 and the people who insist on using BETWEEN for temporal criteria have not.