Select query with date condition
Be careful, you're unwittingly asking "where the date is greater than one divided by nine, divided by two thousand and eight".
Put #
signs around the date, like this #1/09/2008#
SQL query to select dates between two dates
you should put those two dates between single quotes like..
select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date between '2011/02/25' and '2011/02/27'
or can use
select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date >= '2011/02/25' and Date <= '2011/02/27'
keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'
How do I query for all dates greater than a certain date in SQL Server?
select *
from dbo.March2010 A
where A.Date >= Convert(datetime, '2010-04-01' )
In your query, 2010-4-01
is treated as a mathematical expression, so in essence it read
select *
from dbo.March2010 A
where A.Date >= 2005;
(2010 minus 4 minus 1 is 2005
Converting it to a proper datetime
, and using single quotes will fix this issue.)
Technically, the parser might allow you to get away with
select *
from dbo.March2010 A
where A.Date >= '2010-04-01'
it will do the conversion for you, but in my opinion it is less readable than explicitly converting to a DateTime
for the maintenance programmer that will come after you.
How to query DATETIME field using only date in Microsoft SQL Server?
use range, or DateDiff function
select * from test
where date between '03/19/2014' and '03/19/2014 23:59:59'
or
select * from test
where datediff(day, date, '03/19/2014') = 0
Other options are:
If you have control over the database schema, and you don't need the
time data, take it out.or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...
Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)
or, in more recent versions of SQL Server...
Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)
then, you can write your query as simply:
select * from test
where DateOnly = '03/19/2014'
how to query where date with time = date without time in ms sql
Try like this
SELECT * FROM Bookings WHERE Convert(VARCHAR(10),StartTime,101) = Convert(Varchar(10),'2/15/2014',101)
If you are using SQL SERVER 2012
Try this
SELECT * FROM Bookings WHERE FORMAT(StartTime,'M/dd/yyyy') = FORMAT('2/15/2014','M/dd/yyyy')
SQL FORMAT
Select data between a date/time range
You need to update the date format:
select * from hockey_stats
where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00'
order by game_date desc;
Related Topics
Deleting Duplicates Rows from Redshift
SQL Find Difference Between Previous and Current Row
SQL Left Join Losing Rows After Filtering
Update and Select in One Query
How to Find Out Whether a Table Has Some Unique Columns
Postgresql Select Until Certain Total Amount Is Reached
SQL Server 2008 Insert with While Loop
Query Combinations with Nested Array of Records in JSON Datatype
How to Get a Value from Previous Result Row of a Select Statement
How to Specify in Clause in a Dynamic Query Using a Variable
Postgres Syntax Error at or Near "If"
Select "Where Clause" Evaluation Order
"Ora-01438: Value Larger Than Specified Precision Allowed for This Column" When Inserting 3
How to View All the Metadata of Columns of a Table in Oracle Database