CODEWITHSUNDEEP
sqlsql-server
user1413
user1413

Reputation: 537

How to find tables in database with date range SQL?

I have a table wit customer with arrival date and departure date in a format '2014-25-11 00:00:00.000'. I am creating a query with to select those customer but I am having a error

Conversion failed when converting date and/or time from character string.

But I want to find only with date 2014-25-11

SQL query used

select FirstName, LastName 
from customer 
where ArrivalDate > '%2014-25-11 00:00:00.000%' 
  and DepartureDate < '%2014-29-11 00:00:00.000%'

Can anyone help me to write that query?

Upvotes: 1

Views: 150

Answers (4)

A ツ
A ツ

Reputation: 1267

i'm afraid you have to order the date parts so that its ordered year-month-date. this way the 26th Nov 2014 is between arrival and departure even if you compare the strings. The way your date-string is built, it does not sort that way. 2014-26-11 is just not between 2014-25-11 and 2014-29-11.

select * 
from customer c
outer apply ( 
  select ArrivalDate   = substring(c.ArrivalDate,1,4)
                        +substring(c.ArrivalDate,9,2)
                        +substring(c.ArrivalDate,6,2)
       , DepartureDate = substring(c.DepartureDate,1,4)
                         +substring(c.DepartureDate,9,2)
                         +substring(c.DepartureDate,6,2)
) p
where '20141126' between p.ArrivalDate and p.DepartureDate

Upvotes: 0

BJones
BJones

Reputation: 2460

If you want FirstName and LastName you need a comma rather than "and". Also remove the % wildcard. The below queries work in MS SQL Server.

SELECT FirstName, LastName 
FROM customer 
WHERE CAST(ArrivalDate AS DATE) = '11/25/2014';

If you would like values returned between two dates similar to what you have above, try:

SELECT FirstName, LastName 
FROM customer 
WHERE CAST(ArrivalDate AS DATE) BETWEEN '11/25/2014' AND '11/29/2014';

Upvotes: 0

PaperTank
PaperTank

Reputation: 69

Conversion failed when converting date and/or time from character string.

Delete '%' from your values

Try this one:

select FirstName and LastName 
from customer 
where ArrivalDate > CAST('2014-25-11' AS DATE) 
  and DepartureDate < CAST('2014-29-11' AS DATE)

Upvotes: 1

ivan
ivan

Reputation: 1205

if you only want to find the ones in the day 2014-25-11 you could use this

  select FirstName and LastName from customer
   where DATEPART(YEAR,ArrivalDate) = 2014 
and DATEPART(month,ArrivalDate) = 25 

and DATEPART(day,ArrivalDate)= 11

Upvotes: 0

Related Questions