datetime question

Hi,
I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
following
declare @.d1 as datetime, @.d2 as datetime
--Query Set#1
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.001'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#1 Results
yes
yes
--Query Set#2
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#2 Results
no
yes
--Query Set#3
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:01:00.000'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#3 Results
no
no
I am positive that these are not anomalies. So, if someone could explain
these results, it would be great.Hi
LIKE from BOL:
"If any of the arguments are not of character string data type, Microsoft
SQL ServerT converts them to character string data type, if possible."
A LIKE does string comparisons. It uses the default CONVERT to String data
type so:
declare @.d1 as datetime, @.d2 as datetime
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
select convert(char(30), @.d1)
select convert(char(30), @.d2)
Results in:
Mar 26 1995 1:00AM
Mar 26 1995 1:00AM
If you need to do like on non-string datatypes, tell SQL exactly how to
convert the data.
select convert(char(30), @.d1, 113)
select convert(char(30), @.d2, 113)
Results in:
26 Mar 1995 01:00:00:000
26 Mar 1995 01:00:00:003
That will give correct results.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"payyans" <payyans@.discussions.microsoft.com> wrote in message
news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
> Hi,
> I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
> following
> declare @.d1 as datetime, @.d2 as datetime
> --Query Set#1
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.001'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#1 Results
> yes
> yes
> --Query Set#2
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#2 Results
> no
> yes
> --Query Set#3
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:01:00.000'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#3 Results
> no
> no
> I am positive that these are not anomalies. So, if someone could explain
> these results, it would be great.|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> LIKE from BOL:
> "If any of the arguments are not of character string data type, Microsoft
> SQL ServerT converts them to character string data type, if possible."
> A LIKE does string comparisons. It uses the default CONVERT to String data
> type so:
> declare @.d1 as datetime, @.d2 as datetime
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> select convert(char(30), @.d1)
> select convert(char(30), @.d2)
> Results in:
> Mar 26 1995 1:00AM
> Mar 26 1995 1:00AM
> If you need to do like on non-string datatypes, tell SQL exactly how to
> convert the data.
> select convert(char(30), @.d1, 113)
> select convert(char(30), @.d2, 113)
> Results in:
> 26 Mar 1995 01:00:00:000
> 26 Mar 1995 01:00:00:003
> That will give correct results.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "payyans" <payyans@.discussions.microsoft.com> wrote in message
> news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
>
>