Questions related to date or date-related functions, data-types, representations (how dates might be stored and displayed) and operations (such as subtracting dates, adding intervals to dates).
Questions tagged [date]
347 questions
57
votes
6 answers
How to combine date and time to datetime2 in SQL Server?
Given the following components
DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'
What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'?
Some things which don't work are…
Martin Smith
- 87,941
- 15
- 255
- 354
25
votes
2 answers
Can't default date to CURRENT_TIMESTAMP in MySQL 5.5
I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.
Query is
ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
While it is working fine on my local DB with mysql V5.6.56.
urfusion
- 353
- 1
- 4
- 9
18
votes
1 answer
How do I generate a date series in PostgreSQL?
If you're looking to generate a time series, see this question
Let's say that I want to generate a series of dates between two dates.
I see the function generate_series provides only
Function Argument Type …
Evan Carroll
- 65,432
- 50
- 254
- 507
16
votes
6 answers
Determine 3rd Friday of each month
I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server.
I expect I should use a combination of DENSE_RANK() and PARTITION BY() to set "rank = 3". However, I am new to SQL and…
emcor
- 281
- 1
- 3
- 8
15
votes
3 answers
Fill in missing dates with data value from previous populated date for group
Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table contains the last department for each ticket for each…
Mark Freeman
- 2,293
- 5
- 32
- 54
11
votes
4 answers
Getting midnight of today
I want to get 00:00:00 of a day. Should be done without using CONCAT. What options are there?
We can use CURDATE() function in select query but need to display like 2014-05-05 00:00:00. If NOW() will give like 2014-05-05 12:05:37.
I want add…
Logu
- 179
- 1
- 2
- 6
11
votes
2 answers
MySQL - How to make a DATEDIFF in years?
In my table subventions (grants), I created a trigger to be able to calculate the annual amount of money of a grant, using my columns subventions.début (beginning), subventions.fin (end) and subventions.montant (amount).
As DATEDIFF only gives me an…
MDF
- 113
- 1
- 1
- 7
9
votes
1 answer
Generate Dates between Date Ranges in mysql
Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9.
Is there an inbuilt function which I can use to generate dates between date range?
We…
j10
- 309
- 1
- 8
- 16
9
votes
1 answer
What exactly is INTERVAL 1 HOUR checking?
MariaDB 10.1.x
Let's say I have a query like:
select FOO from db.BLAH where STAMPFOO > NOW() - INTERVAL 1 HOUR
If I run that query at 12:15 PM, will I only get records that have a date value greater than 11:15 AM? Or greater than 11:00 AM?
Mike B
- 617
- 4
- 10
- 17
8
votes
4 answers
get all dates in the current month
i have a query in MySQL which serves me very well by getting all the records within the current month;
SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH);
The above query works well. so if this month we only had two…
indago
- 545
- 4
- 12
- 23
8
votes
2 answers
Is an invalid date considered the same as a NULL value?
I am using MySQL 5.7 and I have notice something which I cannot explain to myself with my current knowledge so here it goes.
Is an "invalid" date 0000-00-00 considered same as NULL for a date column type with no possibilities for nullables? See the…
ReynierPM
- 1,888
- 10
- 31
- 49
8
votes
1 answer
Parsing DATE while copying csv file into PostgreSQL table
I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads these columns expecting an ISO format "yyyy/mm/dd",…
baggiponte
- 183
- 1
- 1
- 6
7
votes
3 answers
Convert date in date list condition to list of date ranges
I want to look for all the records that occur on specific dates.
SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);
However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there…
msemelman
- 123
- 6
7
votes
1 answer
Find most frequent values for a given column
I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a descending fashion.
Reading through some questions, this…
driftavalii
- 173
- 1
- 1
- 3
7
votes
1 answer
Generic end date of quarter, PostgreSQL
I'd like to produce the generic quarter end date for a given date.
Ex:If I have 2010-01-01, I would like to return 2010-03-31, and so on.
I can get the quarter number and year:
select to_char(date_trunc('quarter', current_date)::date,…
mountainclimber11
- 759
- 1
- 10
- 23