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
3
votes
4 answers

sql server date difference when only month and year mentioned

Given the 2 months and years. NO day mentioned. I need to calculate the difference between those two dates in SQL.Example of the columns in SQL server DB. MonthFROM YearFROM MonthTo YearTO 12 2010 1 2012 Output should…
RookieAppler
  • 1,517
  • 5
  • 22
  • 58
3
votes
2 answers

MySQL Index on date field

I'm using MySQL database. In that i'm having sales datas in one table and i've created an index for the Date column (i.e) OrderedDate. The datas are retrieved fast when we use the query like SELECT CustID ,CustName ,CustPhone FROM …
CarlJohn
  • 727
  • 2
  • 9
  • 20
3
votes
2 answers

Doctrine 2 DATE_ADD mysql function with computed interval value

I have a query that gives me a unix timestamp calculated selecting the datetime value of a table field and then adding another value of the table. The query is something like the following: SELECT UNIX_TIMESTAMP(DATE_ADD(mydatetimefield, INTERVAL…
Stefano
  • 3,213
  • 9
  • 60
  • 101
2
votes
3 answers

sqlite function for displaying month in Given Date?

I want display month in a given date by using sqlite date Functions. For Example 30-March-2012. I want display only March from that Date.
Venkat
  • 343
  • 1
  • 7
  • 17
2
votes
1 answer

How to use conditions in SQLite (like if-statements, etc.)

I have a table in a database with one column containing dates and another one containing scores. Basically, what I want to do is grab the best score in a given week. Weeks can start on any given day (From Friday to Thursday, for instance), and that…
Jumbala
  • 4,764
  • 9
  • 45
  • 65
2
votes
2 answers

Join on closest date in SQL

Hi I have two tables which I have to join on ID field and then on closest date ( only if the date is within one hour range more or less), the reason is both tables are not updated at same time so time is little off between the two ( but max delay is…
PythonDeveloper
  • 289
  • 1
  • 4
  • 24
2
votes
3 answers

SQL Server shows me different RowCounts using Scalar-Valued Functions

I have a scalar-valued function called DATEONLY that returns DATEADD(DD,0, DATEDIFF(DD,0, @DATETIME)), just like this: CREATE FUNCTION [DBO].[DATEONLY] ( @DATETIME DATETIME ) RETURNS DATETIME BEGIN RETURN DATEADD(DD, 0, DATEDIFF(DD, 0,…
Eduardo Maia
  • 587
  • 1
  • 5
  • 10
2
votes
2 answers

Response time of two messages in the same chat

The scenario A chat_channel can have multiple MESSAGE_ID which is displayed on separate rows. INSERT INTO messages_tbl (message_id, chat_channel, user_type, message_type, …
Callum Jones
  • 55
  • 6
2
votes
1 answer

Grouping by contiguous dates, ignoring weekends in SQL

I'm attempting to group contiguous date ranges to show the minimum and maximum date for each range. So far I've used a solution similar to this one: http://www.sqlservercentral.com/articles/T-SQL/71550/ however I'm on SQL 2000 so I had to make some…
Colin
  • 846
  • 7
  • 16
2
votes
3 answers

How to optimize mysql group by with DATE_FORMAT

I have a sql. select count(id) as total, DATE_FORMAT(create_time,"%Y-%m-%d") as create_date from table_name group by DATE_FORMAT(create_time,"%Y-%m-%d"); Then Definition of column create_time. `create_time` timestamp NOT NULL DEFAULT…
dai
  • 1,025
  • 2
  • 13
  • 33
2
votes
2 answers

How to extract the date part from a timestamp field in apache drill?

I have a timestamp field in a drill table (say date_time), How can I extract the date field only from the same. Saw lots of date operation functions here but none of them helped. Here is the sample data, +------------------------+ | date_time …
Devas
  • 1,544
  • 4
  • 23
  • 28
2
votes
4 answers

Updating table with business day and calendar day

I have a table in SQL Server 2012 that is updated manually every month to reflect what date is a file expected to come in. The date rule already has values but the expected date column is what is updated manually. If its expected on BD1(Business…
Nakia Downer
  • 27
  • 3
2
votes
3 answers

MySQL selecting week number and year causes problems with last week of year

I have a large dataset spanning several years and I am having problems getting a string such as "53-2016" (week 53 in 2016). My query is: SELECT date, DATE_FORMAT(date, "%v-%Y") AS week, FROM myTable GROUP BY week ORDER BY date; Using…
Developer
  • 736
  • 7
  • 30
2
votes
3 answers

Convert varchar time (for time > 24 hours) to float

I have a column with time records that represent the difference between two dates. Some of these records exceed 24 hours. As time syntax doesn't account for time records > 24 hours, I've had to convert these records to varchar hh:mm:ss as outlined…
jamjam1000
  • 41
  • 5
2
votes
5 answers

Oracle to_date function with quarter-format

I need to find some records created in a range of quarters. For example, I'm looking for all records created between the 4th quarter of 2008 and the 1st quarter of 2010. I have this in my WHERE-clause: ...and r.record_create_date between…
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
1 2
3
…
18 19