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
2
votes
1 answer

how to create ISO-8601 gregorian date table in postgres

I'm looking to create a date table in a postgres database. The sample data is expected to look like this: date key = 00001 calendar_date= 1/1/2015 week_num= 1 month_num= 1 month_name= Jan quarter_num= 1 calendar_year= 2015 iso_dayofweek=…
noober
  • 1,427
  • 3
  • 23
  • 36
2
votes
1 answer

How to use GETDATE() in Oracle to find number of days between Current Date and some specified date?

Suppose I have a table called projects and it has columns as projectid, startdate and enddate. The startdate of each project is specified already and the enddate is specified only for projects that are already finished. For ongoing projects, the…
ronilp
  • 455
  • 2
  • 9
  • 25
2
votes
1 answer

SQL : Create Extra Column Specifying Shipping Deadline Date Time

I am querying my DB to make an output that will specify the shipping deadlines for each order. To do this I would like to do a query on a past weeks orders having a column for the date/time of the order. However, I would also like to automatically…
urbanMethod
  • 55
  • 1
  • 1
  • 4
2
votes
3 answers

Difference between dates - sql server

I need to get the following result between two dates: date_start = 01/01/2010 date_end = 10/21/2012 result: 1 year, 9 months and 20 days. I tried the code bellow, but it didn't work. It returns negative dates sometimes: SELECT CAST(DATEDIFF(yy,…
williancsilva
  • 151
  • 1
  • 3
  • 12
2
votes
4 answers

How do I group DATE field by YEAR-MM in SQL Server?

I have a date field in a query and I do want to get GROUP BY report like this: DATE COUNT 2010-01 10 2010-02 2 ... 2010-12 24 2012-13 34 What is the proper syntax to obtain this on SQL Server?
sorin
  • 161,544
  • 178
  • 535
  • 806
1
vote
1 answer

Get dates for last 30 days dynamically in SQL

I have below SQL which gives me count of files received in particular country according to date. But here dates are hard coded. I want them dynamically. I want it in such a way that whenever I run this query, I get result for last 30 days. Below is…
AKHIL OMAR
  • 47
  • 6
1
vote
1 answer

How to sum multiple hour : mins in sql?

i need to sum this varchar values. time 1 = '13:06' time 2 = '18:59' time 3 = '14:49' i tryed this. SELECT convert( char(8), dateadd( second, SUM( DATEPART( hh, (convert(datetime,'12:03',1)) …
Dinesh Thangadurai
  • 11
  • 2
1
vote
2 answers

Getting week of the month. Week should start on Monday and end a Sunday

I need to add new business wee to my dim_date actually i have week of the month that is counting every 7 days of month as the table bellow. Date Week of…
Fernando Gomes
  • 19
  • 2
1
vote
5 answers

Date function on oracle

I've got a question about date function on oracle. I have the following table statistic_table( pages AS varchar(10), date_created AS date ); I have the following sql SELECT COUNT(*) FROM statistic_table WHERE date_created BETWEEN sysdate-5…
gumpi
  • 281
  • 1
  • 4
  • 13
1
vote
3 answers

Adding a day in Oracle but losing hour and minute, and format is also changing

I have a table tab1 in which a column col1 has data type VARCHAR2(50 BYTE) and this column has values like '9/27/21 18:05' I want to add 1 day to this and I am expecting a result like '9/28/21 18:05' If I do TO_TIMESTAMP(col1,'MM/DD/YYYY HH24:MI') +…
Amit Kumar Tyagi
  • 51
  • 6
1
vote
2 answers

In DialogFlow when date is displayed it come with YYYY-MM-DD HH-MM-SS format how to convert to DD-MM-YYYY format?

The response given by the bot for date given by user for example 20th july is 2021-07-20T12:00:00+05:30. How to convert this to 2021-07-20?
IDK
  • 27
  • 5
1
vote
2 answers

(SQL BigQuery) Using Lag but data contains missing months

I have the following table with monthly data. But we do not have the third month. DATE FREQUENCY 2021-01-01 6000 2021-02-01 4533 2021-04-01 7742 2021-05-01 1547 2021-06-01 9857 I want to get the frequency of the previous month…
user16438416
  • 35
  • 4
1
vote
1 answer

Getting average of count(column) grouped by date, but meeting certain conditions

Data sample: dtime id 2021-01-01 06:00:00 1 2021-01-01 06:00:00 2 2021-01-01 06:00:00 3 ... ... 2021-01-01 12:00:00 1 2021-01-01 12:00:00 2 2021-01-01 12:00:00 3 ... ... ... ... 2021-01-12 20:00:00 1 2021-01-12…
fseixas
  • 65
  • 6
1
vote
1 answer

How can I compare a Unix epoch timestamp with a DATE in SQL?

How can I compare a Unix epoch timestamp with a DATE in SQL? For example I have a DATE data_type column with one entry 14-DEC-20 (i.g to_date('14-DEC-20','DD-MON-RR')) and an epoch date equivalent to this. I want to check if the the DATE is…
Sir. Hedgehog
  • 1,260
  • 3
  • 17
  • 40
1
vote
1 answer

How to create a complex aggregate function in sqlite

Suppose I have the following table where there are two sets of observation dates (2015-01-01, 2016-01-01) in the first column. For each observation date, there are associated item_date and…
kyc12
  • 349
  • 2
  • 15
1 2 3
…
18 19