33

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2). Documentation Important quote: Accuracy Rounded to increments of .000, .003, or .007 seconds


27

In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question: yyyyMMdd -- unseparated, date only yyyy-MM-ddThh:mm:ss.fff -- date dash separated, date/time separated by T However, it was recently brought to my attention that there is a third format that is ...


25

Storing the data in a single column is the preferred way, since they are inextricably linked. A point in time is a single piece of information, not two. A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time"...


24

'2013-08-25T17:00:00+00:00' This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect. The documentation explains what ALLOW_INVALID_DATES does: Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. In other words, 2013-02-31 would be a ...


24

You can't get an empty string because you're returning the DATE value type from ISNULL. Per the docs, ISNULL Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int. If ...


22

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure. MySQL docs for NOW() also state: NOW() returns a constant time that indicates the ...


21

One option is a recursive CTE: DECLARE @StartDate datetime = '2017-03-05' ,@EndDate datetime = '2017-04-11' ; WITH theDates AS (SELECT @StartDate as theDate UNION ALL SELECT DATEADD(day, 1, theDate) FROM theDates WHERE DATEADD(day, 1, theDate) <= @EndDate ) SELECT theDate, 1 as theValue FROM theDates ...


19

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME: Time range - 00:00:00 through 23:59:59.997 Note that the time range can never be .999. Further down in the documentation it specifies the ...


19

Personally if it's a date, or can be a date, I suggest always storing it as one. It's just easier to work with as a rule of thumb. A date is 4 bytes. A smallint is 2 bytes (we need two) ... 2 bytes: one smallint for year ... 2 bytes: one smallint for month You can have one date which will support day if you ever need it, or one smallint for year and month ...


18

You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads generate_series for both inputs. Problems with timestamp with timezone You can see the drawback here. SET timezone = 'America/Santiago'; SELECT generate_series(...


17

Another aspect: SYSDATE() can be different, but NOW() cannot. mysql> select now(6), sysdate(6), now(6), sysdate(6); +----------------------------+----------------------------+----------------------------+----------------------------+ | now(6) | sysdate(6) | now(6) | sysdate(6) | +----...


16

Since you are using datetime datatype, you need to understand how sql server rounds datetime data. ╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗ ║ Name ║ sn ║ Minimum value ║ Maximum value ║ Accuracy ║ Storage ║ ╠═══════════╬═════╬═════════════════════════════╬══════...


15

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler: SELECT count(*) AS count_days_no_weekend FROM generate_series(timestamp '2014-01-01' , timestamp '2014-01-10' , interval '1 day') the_day WHERE extract('ISODOW' FROM the_day) < 6; You don't need an extra subquery level for ...


13

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are: yyyyMMdd yyyyMMdd hh:nn:ss[.mmmmmmm] yyyy-MM-ddThh:nn:ss[.mmmmmmm] ----------^ yes, that T is important! Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:* SET ...


13

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a ...


13

VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102)); Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime. (ignoring the redundant convert to datetime) You're then converting that to a string with 102 style (rather than 105 for Italian). You're then relying on an implicit conversion back to datetime to match ...


13

The DateAdd function is what you are looking for. Use millisecond as the first parameter to the function, to tell it that you are adding milliseconds. Then use 1 as the second parameter, for the number of milliseconds to add. Here is an example, grabbing the current time into a variable, and then adding one millisecond to it and saving the result as a ...


13

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row. In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.). Select to show ...


13

From the documentation: A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access ...


11

I have three suggestions SUGGESTION #1 : Rewrite the query You should rewrite the query as follows SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND ) GROUP BY http ORDER BY count; or SELECT * FROM ( SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() ...


11

I'm going to provide a dissenting opinion to the other answers. If both the date and time components are required together i.e. an entry is invalid if it contains one but not the other (or is NULL in one but not the other), then storing it in a single column makes sense for the reasons given in other answers. However, it may be the case that one or both ...


11

This is due to a strange limitation1 in the accuracy of the datetime data type, as documented: Accuracy | Rounded to increments of .000, .003, or .007 seconds The solution would be to use datetime2, which provides better accuracy, as you can see in this dbfiddle demo: The return type of the DATEADD is dynamic based on what you send into it. So the ...


10

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around. Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why? DECLARE @d TABLE(d DATE); ...


10

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming. Here what you have to do: SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5, (6-WEEKDAY(NOW())), (5-WEEKDAY(NOW()))) DAY); The query meaning: With DATE_ADD you will add an interval between the parameter NOW() ...


10

I must first compliment you on your courage to do something like this with an Access DB, which from my experience is very difficult to do anything SQL-like. Anyways, on to the review. First join Your IIF field selections might benefit from using a Switch statement instead. It seems to be sometimes the case, especially with things SQL, that a SWITCH (more ...


10

You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data: SELECT eventtype, eventdate, LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype, LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate FROM atable WHERE eventdate >= '2015-01-01 00:00:00.00' AND eventdate < '...


10

To check for overlaps, you only need 2 conditions, the start or interval A with the end of interval B, and vice versa: WHERE '2019-04-08 13:30:00.000' < Stop AND Start < '2019-04-08 15:30:00.000'


9

Implicit Conversion I supposed posted_date data type is Datetime. However it does not matter whether the type on the other side is Datetime, Datetime2 or just Time because the string (Varchar) will be implicitly converted to Datetime. With posted_date declared as a Datetime2 (or Time), the posted_date <= '2015-07-27 23:59:59.99999' where clause fails ...


9

This takes advantage of the fact that 1900-01-01 is a Monday. So adding n number of weeks will also be a Monday. Here are the datetime values that are being represented by the integers in that expression: dateadd(week, datediff(week, 7 /*'19000108'*/, getdate()), 0 /*'19000101'*/) Adding the number of weeks since 1900-01-08 (the week after the first ...


9

The issue is a combination of datatype granularity / accuracy and source of the values. First, DATETIME is only accurate / granular to every 3 milliseconds. Hence, converting from a more precise datatype such as DATETIMEOFFSET or DATETIME2 won't just round up or down to the nearest millisecond, it could be 2 milliseconds different. Second, the ...


Only top voted, non community-wiki answers of a minimum length are eligible