27787

SQL Count days till first of the month

Question:

How would I count the days from a date till the first of the following month

Example:

--Start Date 07-07-2011

How many days till:

-- The 1st of the succeeding month of the start date above 08-01-2011

Expected Result (in days):

25

So if I counted the day I get 25, so running this query gets me the desired timestamp:

SELECT CURRENT_DATE + INTERVAL '25 DAYS'

Results:

2011-08-01 00:00:00

just can't think of a way to get the number of days, any suggestions?

Or start date, end date, number of days between?

Answer1:

I don't have a PostgreSQL server handy, so this is untested, but I would try:

SELECT (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH') - CURRENT_DATE

Recommend

  • MongoDB Count() vs. Aggregation
  • BigQuery : is it possible to execute another query inside an UDF?
  • Pivot table calculated Field from count of values
  • leaflet marker not displaying in certain contexts
  • How to get list of users who's birthday is today in MongoDB
  • MySql - get days remaining
  • How to use arithmetic operators with SAS macro variables [duplicate]
  • Returning this from a constructor function in JS
  • What is the use of a session store?
  • How to get the date of next specified day of week
  • Easiest way to get current unix timestamp via XSL
  • HttpListener.IsSupported is false on XP SP3
  • How to get data from **Realm database** using **date object**?
  • Find Previous month name using Calender or any classes that in java
  • Saving image to sd with current date and time in name doesn't work
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • Invalid Date on validation Date of js
  • Combining two different ActiveRecord collections into one
  • Stop Bash Script if Hive Fails
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • PHP buffered output depending on server setting?
  • Functions in global context
  • Why querying a date BC is changed to AD in Java?
  • Row Count Is Returning the incorrect number using RaptureXML
  • Illegal mix of collations for operation for date/time comparison
  • Is possible to count alias result on mysql
  • Check if a string to interpolate provides expected placeholders
  • ActionScript 2 vs ActionScript 3 performance
  • Release, debug version and Authorization Google?
  • How can I estimate amount of memory left with calling System.gc()?
  • Apache 2.4 - remove | delete | uninstall
  • php design question - will a Helper help here?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • AngularJs get employee from factory
  • python regex in pyparsing
  • Hits per day in Google Big Query
  • Android Google Maps API OnLocationChanged only called once
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • How can I use threading to 'tick' a timer to be accessed by other threads?