All Questions

Tagged with
Filter by
Sorted by
Tagged with
2181 votes
47 answers
3.7m views

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?
Eddie Groves's user avatar
  • 34.4k
798 votes
19 answers
896k views

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE ...
Fernando Barrocal's user avatar
609 votes
23 answers
998k views

Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11)...
Stephen Perelson's user avatar
560 votes
7 answers
310k views

java.util.Date vs java.sql.Date

java.util.Date vs java.sql.Date: when to use which and why?
flybywire's user avatar
  • 267k
515 votes
17 answers
778k views

How to convert java.util.Date to java.sql.Date?

I am trying to use a java.util.Date as input and then creating a query with it - so I need a java.sql.Date. I was surprised to find that it couldn't do the conversion implicitly or explicitly - but ...
David Ackerman's user avatar
329 votes
6 answers
661k views

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type timestamp without timezone. Client can search ...
user2866264's user avatar
  • 3,309
258 votes
2 answers
420k views

SQL: Subtracting 1 day from a timestamp date

I am using Datagrip for Postgresql. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). I want to be able to: apply a mathematical operator to subtract 1 day filter it ...
J-Ko's user avatar
  • 2,713
244 votes
9 answers
208k views

Best way to work with dates in Android SQLite [closed]

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best ...
Filipe's user avatar
  • 3,398
222 votes
5 answers
488k views

Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do it ...
John's user avatar
  • 6,747
218 votes
14 answers
335k views

Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?
GeoGo's user avatar
  • 2,378
199 votes
24 answers
482k views

Count work days between two dates

How can I calculate the number of work days between two dates in SQL Server? Monday to Friday and it must be T-SQL.
Ovidiu Pacurar's user avatar
187 votes
10 answers
243k views

SQL "between" not inclusive

I have a query like this: SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01' But this gives no results even though there is data on the 1st. created_at looks like 2013-05-01 ...
JBurace's user avatar
  • 5,373
177 votes
16 answers
318k views

Error in MySQL when setting default value for DATE or DATETIME

I'm running MySql Server 5.7.11 and this sentence: updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' is not working. Giving the error: ERROR 1067 (42000): Invalid default value for 'updated' ...
Evhz's user avatar
  • 9,038
165 votes
9 answers
439k views

SQL SERVER: Get total days between two dates

I'm trying to get the total number of days between two days: 1/1/2011 3/1/2011 RETURN 62 Is it possible to do in SQL Server?
Pod Mays's user avatar
  • 2,583
148 votes
19 answers
1.2m views

Select data from date range between two dates

I have a table Named Product_Sales and it holds data like this Product_ID Sold_by Qty From_date To_date 3 12 7 2013-01-05 2013-01-07 6 22 14 2013-01-06 2013-01-10 8 11 9 2013-02-05 2013-02-11 Now ...
Ronjon's user avatar
  • 1,829
147 votes
14 answers
198k views

Group by month and year in MySQL

Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format. I am trying to organize a json object into years / months. json to base the ...
Derek Adair's user avatar
  • 22.1k
139 votes
2 answers
343k views

Difference between datetime and timestamp in sqlserver? [duplicate]

What is the difference between Timestamp and Datetime SQL Server? I thought Both formats are capable of storing date + time. Then, Where the difference is lying between them? But Timestamp is not ...
MduSenthil's user avatar
  • 2,039
131 votes
6 answers
239k views

PostgreSQL: how to convert from Unix epoch to date?

The statement gives me the date and time. How could I modify the statement so that it returns only the date (and not the time)? SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );
sid_com's user avatar
  • 24.6k
128 votes
10 answers
67k views

Comparing date ranges

In MySQL, If I have a list of date ranges (range-start and range-end). e.g. 10/06/1983 to 14/06/1983 15/07/1983 to 16/07/1983 18/07/1983 to 18/07/1983 And I want to check if another date range ...
Kieran Benton's user avatar
128 votes
19 answers
13k views

SQL to determine minimum sequential days of access?

The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per user....
Jeff Atwood's user avatar
  • 63.7k
120 votes
29 answers
334k views

How to determine the number of days in a month in SQL Server?

I need to determine the number of days in a month for a given date in SQL Server. Is there a built-in function? If not, what should I use as the user-defined function?
Even Mien's user avatar
  • 45.2k
119 votes
5 answers
114k views

Get mySQL MONTH() to use leading zeros?

How do I specify to mySQL's MONTH() function to return '08' instead of 8 in this query? I'd like the sort to work datewise. Currently getting results for date like 2006-9 2007-1 2007-10 2007-11 ...
jerrygarciuh's user avatar
  • 21.6k
119 votes
19 answers
396k views

Get the week start date and week end date from week number

I have a query that counts member's wedding dates in the database. SELECT SUM(NumberOfBrides) AS [Wedding Count] , DATEPART( wk, WeddingDate) AS [Week Number] , DATEPART( year, WeddingDate) AS [...
digiguru's user avatar
  • 12.8k
114 votes
12 answers
878k views

Query comparing dates in SQL

I have a table with dates that all happened in the month November. I wrote this query select id,numbers_from,created_date,amount_numbers,SMS_text from Test_Table where created_date <= '2013-04-...
HelpASisterOut's user avatar
102 votes
3 answers
424k views

Oracle SQL - DATE greater than statement

As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query. SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; I've tried the ...
user3521826's user avatar
  • 1,049
101 votes
7 answers
140k views

Find records with a date field in the last 24 hours [duplicate]

In my SQL query how do i make it find the records in the last 24 hours? Eg SELECT * FROM news WHERE date < 24 hours I usually do it by setting a variable to date() - 1 day and comparing it to ...
user1022585's user avatar
  • 13.4k
101 votes
23 answers
166k views

Get a list of dates between two dates

Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:...
Gilgad's user avatar
  • 1,147
100 votes
4 answers
191k views

Extract day of week from date field in PostgreSQL assuming weeks start on Monday

select extract(dow from datefield) extract a number from 0 to 6, where 0 is Sunday; is there a way to get the day of the week in SQL assuming that weeks start on Monday (so 0 will be Monday)?
Eugenio's user avatar
  • 3,355
89 votes
7 answers
136k views

How do I get just the date when using MSSQL GetDate()? [duplicate]

DELETE from Table WHERE Date > GETDATE(); GETDATE() includes time. Instead of getting 2011-01-26 14:58:21.637 How can I get: 2011-01-26 00:00:00.000
sooprise's user avatar
  • 23k
87 votes
10 answers
114k views

handling DATETIME values 0000-00-00 00:00:00 in JDBC

I get an exception (see below) if I try to do resultset.getString("add_date"); for a JDBC connection to a MySQL database containing a DATETIME value of 0000-00-00 00:00:00 (the quasi-null value for ...
Jason S's user avatar
  • 187k
85 votes
7 answers
166k views

Get the latest date from grouped MySQL data

I have the following data in my database: |NO | model | date | +---+-------+----------+ |1 | bee |2011-12-01| |2 | bee |2011-12-05| |3 | bee |2011-12-12| |4 | tar |2011-12-13| I ...
nunu's user avatar
  • 2,781
81 votes
7 answers
73k views

Getting date list in a range in PostgreSQL

I'd like to get the list of days between the two dates (including them) in a PostgreSQL database. For example, if I had: start date: 29 june 2012 end date: 3 july 2012 then the result should be: 29 ...
Javi's user avatar
  • 19.5k
80 votes
7 answers
229k views

How do I extract Month and Year in a MySQL date and compare them?

How do I extract the month and date from a mySQL date and compare it to another date? I found this MONTH() but it only gets the month. I looking for month and year.
JohnSmith's user avatar
  • 1,507
79 votes
2 answers
106k views

Selecting by month in PostgreSQL

I want to select rows according to the month of a date or timestamp column like this: SELECT id, name, birthday FROM employee.person WHERE Month(birthday) > 10; But I only get error messages in ...
aulia's user avatar
  • 811
78 votes
4 answers
96k views

How to format bigint field into a date in Postgresql?

I have a table with a field of type bigint. This field store a timestamp. I want to date format the field like this : to_char( bigint_field,'DD/MM/YYYY HH24:MI:SS') I get the following error : ...
Stephan's user avatar
  • 42.4k
77 votes
7 answers
494k views

How do I get the current year using SQL on Oracle?

I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL? i.e. BETWEEN TO_DATE('01/01/**currentYear** 00:00:00', 'DD/MM/YYYY HH24:MI:SS')...
Craig Angus's user avatar
75 votes
7 answers
469k views

Get week day name from a given month, day and year individually in SQL Server

I am trying get a day name like friday, saturday, sunday, monday etc from a given date. I know there is a built in function which returns the day name for example: SELECT DATENAME(dw,'09/23/2013') as ...
theITvideos's user avatar
  • 1,492
74 votes
11 answers
372k views

How to compare two dates to find time difference in SQL Server 2005, date manipulation

I have two columns: job_start job_end 2011-11-02 12:20:37.247 2011-11-02 13:35:14.613 How would it be possible using T-SQL to find the raw amount of time that has ...
some_bloody_fool's user avatar
71 votes
7 answers
457k views

Get month name from date in Oracle

How to fetch month name from a given date in Oracle? If the given date is '15-11-2010' then I want November from this date.
Niraj Choubey's user avatar
70 votes
5 answers
159k views

Query to convert from datetime to date mysql

I am trying to get the date portion of a datetime field. I know I can get it with date_format, but that returns a string or "varchar" field. How can I convert the result to date and not as varchar? ...
chupeman's user avatar
  • 1,095
66 votes
6 answers
208k views

SQL Server 2005 Using DateAdd to add a day to a date

How do I in SQL Server 2005 use the DateAdd function to add a day to a date
test's user avatar
  • 2,107
66 votes
5 answers
535k views

How to get DATE from DATETIME Column in SQL? [duplicate]

I have 3 columns in Table TransactionMaster in sql server 1) transaction_amount 2) Card_No 3) transaction_date-- datetime datatype So, I want to fetch SUM of transaction_amount where Card_No=' ...
user avatar
66 votes
5 answers
132k views

sqlite select with condition on date

I have an sqlite table with Date of Birth. I would like to execute a query to select those records where the age is more than 30. I have tried the following but it doesn't work: select * from mytable ...
Thunder's user avatar
  • 10.6k
63 votes
5 answers
102k views

How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?

I want to subtract between two date time values using SQL in MySQL such that I get the interval in minutes or seconds. Any ideas? I want to run a SQL query that retrieves uses from a database who have ...
Ali's user avatar
  • 7,433
61 votes
3 answers
217k views

Get records of current month [duplicate]

How can I select Current Month records from a table of MySql database?? Like now current month is January. I would like to get records of January Month, Where data type of my table column is ...
Foysal Vai's user avatar
  • 1,365
58 votes
7 answers
217k views

Select from table by knowing only date without time (ORACLE)

I'm trying to retrieve records from table by knowing the date in column contains date and time. Suppose I have table called t1 which contains only two column name and date respectively. The data ...
Abdulrhman's user avatar
58 votes
4 answers
437k views

Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-...
Avinesh Kumar's user avatar
56 votes
4 answers
45k views

Insert/ Update random date in MySQL

How would I update a column with a random date in the past 2 weeks using MySQL? For example (code doesn't actually work): UPDATE mytable SET col = sysdate() - rand(1, 14);
Blair's user avatar
  • 3,701
56 votes
3 answers
237k views

#1292 - Incorrect date value: '0000-00-00' [duplicate]

I referenced many questions which have the same title as mine, but they have a different approach and different issue so this question is not a duplicate. I have a table in which column fm_sctrdate ...
urfusion's user avatar
  • 5,475
56 votes
12 answers
428k views

SQL ORDER BY date problem

Can you please help me in solving this problem. I am trying to order the results of an SQL query by date, but I'm not getting the results I need. The query I'm using is: SELECT date FROM tbemp ORDER ...
dnyaneshwar's user avatar

1
2 3 4 5
159