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

Add Missing monthly dates in a timeseries data in Postgresql

I have monthly time series data in table where dates are as a last day of month. Some of the dates are missing in the data. I want to insert those dates and put zero value for other attributes. Table is as follows: id report_date price 1 …
liferacer
  • 2,473
  • 2
  • 17
  • 16
3
votes
1 answer

way for using DATE_SUB(CURDATE(),INTERVAL 2 DAY) condition in cakephp

i am using cakephp and creating this condition: $conditions = array("Userword.levelid"=>0, "Userword.userid"=>$this->Auth->user('UserId'),"date(Userword.date)"=>"DATE_SUB(CURDATE(),INTERVAL 0 DAY)"); $Userword = $this->paginate(null,…
aya
  • 1,597
  • 4
  • 29
  • 59
3
votes
2 answers

To find Lowest Experience of any Employee by writing a query in GRID DB

I am working on a EmployeeDetails table. I have 4 columns in that table(Empid,Empname, DateOfJoining, Salary). I want to write a query to fetch the Empid,Empname and salary of the employee who is having Lowest Experience. Can someone please…
Harshal
  • 31
  • 1
3
votes
4 answers

Get the max possible time of a date SQL Server

What I am trying to do is take a date in SQL Server and find the last possible hour, minute, second, and millisecond of that date. So if the date is this: 2021-02-16 13:08:58.620 I would like to return: 2021-02-16 23:59:59.999 I have tried something…
Eric
  • 212
  • 2
  • 15
3
votes
1 answer

Format string to datetime using Spark SQL

I am trying to convert and reformat a date column stored as a string using spark sql from something that looks like this... 30/03/20 02:00 to something that is a datetime column and looks like this... 2020-03-30 02:00 ('YYYY-MM-dd HH:mm') I am not…
bbal20
  • 113
  • 4
  • 11
3
votes
2 answers

SQL Server FORMAT with culture parameter not working

I am using SQL Server 2017 and try to use culture in the Format function. When running this Query on the server the result is incorrect: SELECT @@VERSION --> Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64) Sep 13 2019 15:49:57…
Fred
  • 3,365
  • 4
  • 36
  • 57
3
votes
1 answer

Getting exact day values per month, between 2 dates

I am trying to get the amount of days in a month, between 2 dates. So this would ideally look like Month | Days Jan | 5 Feb | 28 March | 5 The dates need to come from a table that looks like this - Name | Age | Address |…
sam smith
  • 107
  • 1
  • 8
3
votes
4 answers

Retrieve Year and week number from date in SQL Server

I am trying to fetch the year number and week number in the format yyyyww like 201901, 201905, 201911 etc and I am using this query to do it: declare @BeginDate datetime set @beginDate = '2019-02-07' select concat(datepart(year,…
Sebastian
  • 4,625
  • 17
  • 76
  • 145
3
votes
3 answers

Need to subtract some hours from given timestamp in hive

Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss') Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37 I know that there is date_sub function in hive but it is only used to subtract days from the given…
Bhuvi007
  • 111
  • 1
  • 3
  • 11
3
votes
3 answers

How to do I exclude a specific date range within a specific period?

I am using SQL Server 2012 and I need my T-SQL query to exclude a specific time range within a specific period. My filtering logic stands as follows: Extract all data which falls between 2016-07-01 and 2017-03-25 AND between 2017-07-01 and…
user3115933
  • 4,303
  • 15
  • 54
  • 94
3
votes
2 answers

Netezza What is the best way to get the first day of the month for a date?

Select to_date(to_char(date_part('year',current_date),'0000') || trim(to_char(date_part('month',current_date),'00')) || '01','YYYYMMDD') So far, this is the best I can come up with. I am also unable to find a comprehensive language reference for…
Nathan T Alexander
  • 257
  • 3
  • 15
3
votes
0 answers

STR_TO_DATE returns null in SELECTbut throws an error in UPDATE

I'm trying to convert strings in one column to dates in a second column. The dates have varying timezones, so the STR_TO_DATE function chokes a bit. createdTime : Thu Oct 08 09:28:18 MDT 2009 I can successfully run this statement to get a parsed…
Indigenuity
  • 9,332
  • 6
  • 39
  • 68
3
votes
4 answers

SQl Difference in date

I need find difference in two date in years but years should be in decimal example i need to find difference between 16-jun-2010 to 30-Sep-2014 and it should be 4.30 yrs i try following way select DATEDIFF(YY,'16-jun-2010','30-sep-2014') as…
user5746445
3
votes
1 answer

How to find a time delta of a datatime row in mysql?

I have a column in a database which provides datetime stamps for series of sensor readings. I'd like to segment those reading into unbroken, continuous sets of sensor readings. If a sensor reading is broken, then there will be a discontinuity in the…
speciousfool
  • 2,620
  • 5
  • 28
  • 33
3
votes
4 answers

Sort Sql Query by Month

I am having a sql query as : select dateName(month, DateAccessed) "Month" , count(1) totalVisits , count(distinct l.userName) UsersVisit from and where clause goes here group by dateName(monthDateAccessed) order by Month The output I get is Month …
hello temp11
  • 141
  • 4
  • 17
1
2
3
…
18 19