Stack Overflow
Stack Overflow

Questions tagged [sql-date-functions]

The SQL-Date-Functions tag is for questions related to database SQL functions that handle date and time information.

For database data stored as date or timestamp data types, SQL has several date functions to perform various calculations. For determining the number of days between two dates, the date values can simply be subtracted.

MS SQL Server specifics

Formatting and Converting

For converting text strings into proper date/time values there are functions such as to_date('13-Dec 2008 13.05.33', 'dd-Mon YYYY hh24.mi.ss') and functions for formatting a date into a text string, such as to_char(hire_date, 'WW-YYYY') which would return the week number and year for the hire_date.

Calculations

Other functions are used for calendaring information, such as last_day(date) which returns the last day of the month for the specified date.

See Also

The tag is used for questions regarding the various types of date/time data types such as DATE and TIMESTAMP.

284 questions
137
votes
10 answers

How to get difference of days/months/years (datediff) between two dates?

I am looking for a way to implement the SQLServer-function datediff in PostgreSQL. That is, this function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified start date and end…
gefei
  • 18,922
  • 9
  • 50
  • 67
41
votes
5 answers

How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

I want to perform a query which would look like this in native SQL: SELECT AVG(t.column) AS average_value FROM table t WHERE YEAR(t.timestamp) = 2013 AND MONTH(t.timestamp) = 09 AND DAY(t.timestamp) = 16 AND t.somethingelse…
Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
38
votes
4 answers

Is there a function that takes a year, month and day to create a date in PostgreSQL?

In the docs I could only find a way to create a date from a string, e.g. DATE '2000-01-02'. This is utterly confusing and annoying. What I want instead is a function that takes three parameters, so I could do make_date(2000, 1, 2) and use integers…
user142019
32
votes
8 answers

First day of the next month

I'm trying get the results where it only displays OrderDates before the LAST day of the CURRENT month. I'm guessing it would be like this... SELECT OrderDate FROM Orders WHERE OrderDate < (code for first day of the next month?)
jaramore
  • 389
  • 1
  • 3
  • 12
23
votes
6 answers

Oracle date function for the previous month

I have the query below where the date is hard-coded. My objective is to remove the harcoded date; the query should pull the data for the previous month when it runs. select count(distinct switch_id) from…
user803860
  • 299
  • 3
  • 4
  • 13
20
votes
2 answers

Mysql DATE_SUB(NOW(), INTERVAL 1 DAY) 24 hours or weekday?

I am trying to get the total amount of registered users per day. At the moment I am using this: $sql = "SELECT name, email FROM users WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) < lastModified" But I am not sure if this works per day or per 24 hours? For…
alex
  • 4,804
  • 14
  • 51
  • 86
19
votes
2 answers

Converting java.sql.Date & java.util.Date to org.joda.time.LocalDate

I am trying to carefully and meticulously clean up some of my older (production) code. One thing I am trying to do is convert all my usages of java.util.Date to LocalDate and DateTime. However, I noticed one big obstacle tonight as I was working. I…
ryvantage
  • 13,064
  • 15
  • 63
  • 112
13
votes
2 answers

Why do two MySQL dates in 1582 appear to be the same but comparison result is false?

I know that Gregorian calendar started on Oct 15th 1582, and during the transition from Julian calendar, 10 days had been dropped. When I'm doing this query: SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d') I'm getting this result: 1582-10-15 (the 10…
Ziv Gabovitch
  • 735
  • 1
  • 7
  • 18
13
votes
3 answers

Curdate() Vs current_date MySql

What is the difference between curdate() and current_date in MySQL?
Bharanidharan
  • 131
  • 1
  • 4
10
votes
1 answer

Convert date to day of year in Postgres

How can I convert a column with a date e.g. '2012-08-03' to the day of year in Postgres?
anna
  • 195
  • 2
  • 9
10
votes
4 answers

last friday of a given month in sql server

How do i get the date for last friday of the month in T-SQL? I will be passing the year and month as parameter,e.g, 201211. If I pass '201211' as parameter it should return me '20121130' as answer as it's the date of last friday of month of…
Abhishek Arora
  • 111
  • 1
  • 1
  • 4
9
votes
3 answers

How to parse String to java.sql.date

I have a String String s = "01 NOVEMBER 2012"; Then I want parse it to sqlDate. And insert it into the database. Is it possible to parse that string to sqlDate?!?! Yup, sql date format is "yyyy-mm-dd"
Jason Amavisca
  • 187
  • 3
  • 5
  • 13
7
votes
4 answers

SQL Select Records based on current date minus two days

I have an orders table which contains an order ID, order date and order description. I want to run a select query which captures all orders that have been created in the last two days. so the current date minus two days. from the 14th December, I…
Emma
  • 103
  • 1
  • 1
  • 6
6
votes
1 answer

Filter data based on date in sql

Im trying to execute the following SQL query and filter out the data based on the date. I need to display a table which filters out the data such that, only those rows which are between the mentioned start_date and end_date Here's the query that I…
shockwave
  • 3,074
  • 9
  • 35
  • 60
5
votes
1 answer

How to get current month firstdate and lastdate in postgres sql query

I want to get first and last dates of current month (Like 30th or 31st).How to get this one by using postgress sql query.
suresh
  • 119
  • 2
  • 3
  • 8
1
2 3
…
18 19