Hot answers tagged

31 votes
Accepted

Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

From the MySQL 5.5 manual: You cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as ...
Philᵀᴹ's user avatar
  • 31.7k
25 votes
Accepted

How do I generate a date series in PostgreSQL?

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 ...
Evan Carroll's user avatar
  • 62.7k
18 votes
Accepted

Generate Dates between Date Ranges in mysql

I tried this solution : WITH recursive Date_Ranges AS ( select '2018-11-30' as Date union all select Date + interval 1 day from Date_Ranges where Date < '2018-12-31') select * from ...
preeti tank's user avatar
16 votes

Fill in missing dates with data value from previous populated date for group

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between. WITH TAwithnext AS (SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY ...
Rob Farley's user avatar
  • 16.2k
15 votes

How to combine date and time to datetime2 in SQL Server?

I'm late to the party but this approach, while similar to @ypercube's answer, avoids the need to use any string conversion (which can be more expensive than date conversions), is deterministic, and ...
knuckles's user avatar
  • 251
15 votes
Accepted

Generic end date of quarter, PostgreSQL

I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a ...
joanolo's user avatar
  • 13.4k
12 votes

Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

Check this answer. Your options are: Upgrade to MySQL 5.6.5 Change the column type to TIMESTAMP, as in: ALTER TABLE `downloads` ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; Create a ...
jynus's user avatar
  • 14.8k
12 votes

Getting midnight of today

The documentation surprisigly contains a solution: SELECT timestamp(current_date); (which is basically the same as a comment above).
András Váczi's user avatar
12 votes

Why does this query fail when I run it from a SQL Server job?

It fails because you aren't using a language neutral datetime format. You are assuming that SQL Server understands that the day comes first, then the month and finally the year. When you run this ...
Tibor Karaszi's user avatar
10 votes
Accepted

How to add a day/night indicator to a timestamp column?

SELECT record_id, rec_date , CASE WHEN rec_date::time < '08:00' THEN 'Night' WHEN rec_date::time >= '20:00' THEN 'Night' ELSE 'Day' END AS indicator FROM records; ...
Erwin Brandstetter's user avatar
10 votes
Accepted

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

The error does occur on SQL Server 2019, as proven with this simple test. Let's check the server version first: SELECT [SQL Server Version] = @@VERSION; Results: SQL Server Version Microsoft SQL ...
Hannah Vernon's user avatar
  • 70k
9 votes
Accepted

Querying a date range

Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also ...
Aaron Bertrand's user avatar
9 votes
Accepted

Add business days in PostgreSQL

You can use generate_series() to generate a series of dates, and extract() to get day of week. Then simply filter those dates where day of week are not 0=Sunday, 6=Saturday. with days as ( ...
McNets's user avatar
  • 23.7k
9 votes

What is the CURRENT_DATE or current date value function for SQL Server?

You can use either GETDATE (return type datetime) or SYSDATETIME (return type datetime2), with the difference being the precision up to nanoseconds for SYSDATETIME(). Example: SELECT GETDATE() ...
Henrico Bekker's user avatar
8 votes
Accepted

What exactly is INTERVAL 1 HOUR checking?

Since the WHERE clause says NOW(), the expression STAMPFOO > NOW() - INTERVAL 1 HOUR makes this a dynamic query. It will return all records whose timestamp is greater than one hour ago, down to the ...
RolandoMySQLDBA's user avatar
8 votes
Accepted

Best way to get current year minus 5 years

There are different ways to achieve the goal. SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual; OR SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'...
atokpas's user avatar
  • 8,624
7 votes
Accepted

SQL Server 2014 accounts for Daylight Saving Time now?

Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014? No, SQL Server 2014 is not "DST-aware", nor is your test showing it to be. First, your 4th and 5th ...
Solomon Rutzky's user avatar
7 votes

Is there a cutoff when using datetime and date datatypes in the where clause?

Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL >= '20160901' and COL < '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in ...
Aaron Bertrand's user avatar
7 votes

Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

Alright... after a change in my approach, I decided to try to "extract" data instead of convert. Turned out there's the oh-so convenient function extract in Postgres, which does exactly what I want. ...
Milkncookiez's user avatar
7 votes
Accepted

Why does this query fail when I run it from a SQL Server job?

I'd say the problem is related to the language being used when the query runs. Please, run this from SSMS and from the job you created to check if the language being used is the same on both methods: ...
Ronaldo's user avatar
  • 5,069
7 votes
Accepted

Aggregate overlapping date intervals

Directly: WITH cte1 AS ( SELECT account_id, start_date the_date, 1 weight FROM Subscriptions UNION ALL SELECT account_id, end_date, -1 FROM Subscriptions ), cte2 AS ( SELECT ...
Akina's user avatar
  • 19.6k
7 votes
Accepted

Optimizing SQL Server Query When Joining Tables on DATETIME column

computin' If I were you and I had to do this repeatedly, I'd probably add computed columns that give you the data types you want, and index them accordingly. ALTER TABLE table1 ADD dt_c AS CONVERT(...
Erik Darling's user avatar
  • 40.5k
6 votes
Accepted

Conversion fails when converting date average to date in sql

This query: gets the number of days between each of your dates and 2000/01/01. calculate the average number of days add the average back to 2000/01/01 SELECT DATEADD(DAY , AVG( ...
Julien Vavasseur's user avatar
6 votes

How to combine date and time to datetime2 in SQL Server?

For SQL Server 2012 and above there is the DATETIME2FROMPARTS function. It has this form: DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision) For the given sample data ...
Michael Green's user avatar
6 votes

How to generate list of dates --- ie every day for the past 2 years?

Using pure T-SQL, you can do the following: ;WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor] FROM [master].[sys].[columns] sc1 CROSS JOIN [master].[sys].[...
Solomon Rutzky's user avatar
6 votes
Accepted

How can I correctly use the timezone format specifier in a TO_DATE call in Oracle

Datetime Format Models TZR - Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in ...
Balazs Papp's user avatar
  • 40.6k
6 votes
Accepted

Switch VARCHAR to DATE in MySQL

Use str_to_date() to handle the conversion. str_to_date('2016-12-4', '%Y-%m-%d');
Evan Carroll's user avatar
  • 62.7k
6 votes

SSIS changes SQL Server source data type from date to WSTR

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008. I suspect the provider isn't detecting the data type as it ...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

How do we get an hour, minute and second difference between two datetimes?

CURRENT_TIMESTAMP returns the current datetime something like 2017-03-09 15:19:53.770. CONVERT(VARCHAR(10), CreatedDate, 108) returns a string with only time 15:19:53. The second parameter of ...
Mikael Eriksson's user avatar

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