Compare 2 char strings?

Hi,

How do i compare 2 dates which are char datatypes?

Example:

declare @.d1 char(24)

declare @.d2 char(24)

SET @.d1 ='2007-04-24 00 :00:00:000'

SET @.d2 = '2007-04-24 00 :00:00:000'

IF(@.d1=@.d2)

print '1'

Else

print '2'

output:

out put is 2 instead of 1.

It works fine if i use >= or <= but not =.

--Kodela.

You should be using "==". "=" is an assignment operator, not an equality operator.|||

Phil Brammer wrote:

You should be using "==". "=" is an assignment operator, not an equality operator.

In T-SQL?|||

Adamus Turner wrote:

Phil Brammer wrote:

You should be using "==". "=" is an assignment operator, not an equality operator.

In T-SQL?

True that. Heck, I just jumped in when he said "==" worked. His code worked fine for me in T-SQL as written.|||

When I paste this code into my query analyzer, I get '1'. What you have there should work. Are you always comparing dates?

Mike Binkley.

|||

Hi Phil,

It didn't work for me.I got this error when i used '=='

Incorrect syntax near '='.

--Kodela.|||

Because you're dealing with timestamps that use seconds, no 2 dates will ever be equal.

01/01/2001 01:01:000 will never be equal to 01/01/2001 01:02:000

You must format the date so there is no timestamp hh:mms to compare.

SELECT CONVERT(CHAR(10),GETDATE(),110), CONVERT(CHAR(10),GETDATE(),110)

--Notice the difference:

SELECT CONVERT(CHAR(10),GETDATE(),110), GETDATE()

These above dates are equal. So you must do the following:

declare @.d1 char(10)

declare @.d2 char(10)

SET @.d1 ='2007-04-24 00 :00:01:000'

SET @.d2 = '2007-04-24 00 :03:21:000' --This is still equal to the above date

IF(@.d1=@.d2)

print '1'

Else

print '2'

Setting char(10) instead of char(24) will accomplish the same task

Adamus

|||

kodela wrote:

Hi Phil,

It didn't work for me.I got this error when i used '=='

Incorrect syntax near '='.

--Kodela.

Yeah, I'm blind. I admit it among my other flaws in this post. Should've kept my fingers away from the keyboard... |||

It worked for you because the seconds are the same. The poster didn't realize he posted correct syntax on accident. If you change the seconds the code will not work.

declare @.d1 char(24)

declare @.d2 char(24)

SET @.d1 ='2007-04-24 00 :00:00:001'

SET @.d2 = '2007-04-24 00 :00:00:000'

IF(@.d1=@.d2)

print '1'

Else

print '2'

Notice the "001" on the seconds. You have to use char(10)

Adamus

|||

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Then you cannot use an = comparison unless you first compare the date and then the time. You are forced to use <= or >= or BETWEEN

But be careful with BETWEEN when using timestamps because it will exclude remainders of days.

Adamus

|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Then the above will work just fine. Just know that if it's one second off, the comparison will not be equal.|||

kodela wrote:

Adamus,

I cannot change char to 10 because i need to compare both date and time.

My query takes a parameter value in datetime and checks whether that datettime exists in table or not and print the output accordingly.

kodela.

Convert to Char(10) to know the days are the same --> then compare Char(24)

Adamus

|||

Do you want to compare DATES or do you want to compare DATE STRINGS?

Code Snippet

declare @.dt1 datetime

declare @.dt2 datetime

declare @.str1 char(24)

declare @.str2 char(24)

--Set the STRING values (notice "A" in april comes before 'N' in 'November)

SET @.str1 ='November 1, 2007'

SET @.str2 = 'April 1, 2008'

--IMPLICIT conversion of STRING values to DateTime values

set @.dt1 = @.str1

set @.dt2 = @.str2

if @.str1 > @.str2

select 'String 1 Is Bigger'

if @.str2 > @.str1

select 'String 2 is Bigger.'

if @.dt1 > @.dt2

select 'Date 1 is Bigger'

if @.dt2 > @.dt1

select 'Date 2 is Bigger'

|||

I'm not sure how this would work at all. What if you're comparing dates in the same month? ...and the month are not in alpabetical order.

Adamus