All Questions
7,934
questions
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?
- 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 ...
- 12.8k
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)...
- 6,753
560
votes
7
answers
310k
views
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 ...
- 12.7k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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?
- 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.
- 8,223
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 ...
- 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'
...
- 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?
- 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 ...
- 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 ...
- 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 ...
- 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 ) );
- 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 ...
- 8,830
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....
- 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?
- 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
...
- 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 [...
- 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-...
- 3,155
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 ...
- 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 ...
- 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:...
- 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)?
- 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
- 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 ...
- 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 ...
- 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 ...
- 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.
- 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 ...
- 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 :
...
- 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')...
- 23k
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 ...
- 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 ...
- 4,635
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.
- 3,992
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?
...
- 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
- 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=' ...
user3239820
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 ...
- 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 ...
- 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 ...
- 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 ...
- 597
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-...
- 1,459
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);
- 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 ...
- 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 ...
- 561