How to compare two date in SQL Server 2005?

Question

How can I convert date value into format of dd-MM-yyyy in SQL? I have stored date into database in varchar(10) format. Now want to compare date from 1-05-2015 to 31-06-2015.

Query is :

select date
from dates
where date >='1-05-2015' and date <='10-06-2015'

I need help in Crystal report. How can i remove blank line in crystal report 2008?

it returns only (same result in use of between)

1. 1-06-2015
2. 10-06-2015
3. 1-07-2015

I cannot use Format Method because 2005 not support. I tried Convert method but not helpful.

Answer 1

You should use this, if you store date as string, to convert it to DATE

https://msdn.microsoft.com/de-de/library/ms187928.aspx

Answer 2

Can't remember whether the CONVERT was exactly like it is now, but you can try

SET DATEFORMAT dmy -- Needed if date format on database server is different so the start and end date string values 01-05-2015 and 10-06-2015 can be converted automatically with the custom format
select date from dates where CONVERT(Date, date, 105) >='01-05-2015' and CONVERT(Date, date, 105) <='10-06-2015'

I would suggest not to store dates as varchar(10) but as Date or DateTime. In addition I would use yyyy-MM-dd format, which can be interpreted regardless of whatever setting so you don't need the SET DATEFORMAT. So if you always store dates as date you can do (among other features):

select date from dates where date >= '2015-05-01' and date <= '2015-06-10'

Answer 3

I agree with the other comments that dates stored in a proper date format are the best way to go, but sometimes changing the database isn't feasible.

Here's some code that will convert the date from your string format to datetime, assuming the month and year are always 2 and 4 characters, with the day being the only variable length.

Note, I changed the dates in the where statement from '1-05-2015' to '2015-05-01' and '10-06-2015' to '2015-06-10'.

select date
       , cast(right(date, 4)+'-'+SUBSTRING(date, charindex('-', date)+1, 2)+'-'+left(date, charindex('-', date)-1) as datetime) as converted_date
from dates
where
     cast(right(date, 4)+'-'+SUBSTRING(date, charindex('-', date)+1, 2)+'-'+left(date, charindex('-', date)-1) as datetime)
         >= cast('2015-05-01' as datetime)
     and cast(right(date, 4)+'-'+SUBSTRING(date, charindex('-', date)+1, 2)+'-'+left(date, charindex('-', date)-1) as datetime)
         <= cast('2015-06-10' as datetime)

Answer 4

I can convert varchar to datetime in sql 2014 using the following code

ALTER TABLE table_you_want ALTER COLUMN column_you_want DATETIME

Well i am not sure whether 2005 supports it or not. hope it helps..!!1

Answer 5

Luckily I had 2005 installed on my machine and i got results.

See this SQL Fiddle

Let me know if this doesn't help you.

Answer 6

You can convert to date type with style:

select date 
from dates
where convert(date, date, 103) between '20150501' and '20150610' 

Never keep dates as strings, numerics as strings etc. Use appropriate types, which will keep you far from problems like this.