Datetime comparison problem

[Sreenivas]

Hi every one,
I need to compare to datetime values , done in
stored procedure.The input datetime parameter is in
dd/mm/yyyy hh:mm:ss AM/PM format ,the datetime values to be compared
are also stored with the same format.
does datetime works fine for AM/PM format also? because some test
conditions are failing , which i thought correct..Help me out!
Thanks in advance..

[Erland Sommarskog]

Re: Datetime comparison problem

Sreenivas (thatiparthysre enivas@gmail.co m) writes:

I need to compare to datetime values , done in
stored procedure.The input datetime parameter is in
dd/mm/yyyy hh:mm:ss AM/PM format ,the datetime values to be compared
are also stored with the same format.
does datetime works fine for AM/PM format also? because some test
conditions are failing , which i thought correct..Help me out!

datetime is a binary data type and does not have a format, least of all
one with AM/PM. If you have data as strings in your application, you
should use parameterised statements, so that the strings are converted
in the client with respect to regional settings.

Show us the code your having problem with, and we should be able to tell
what you are doing wrong.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sreenivas]

Re: Datetime comparison problem

On Nov 1, 6:34 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Sreenivas (thatiparthysre eni...@gmail.co m) writes:

I need to compare to datetime values , done in
stored procedure.The input datetime parameter is in
dd/mm/yyyy hh:mm:ss AM/PM format ,the datetime values to be compared
are also stored with the same format.
does datetime works fine for AM/PM format also? because some test
conditions are failing , which i thought correct..Help me out!

>
datetime is a binary data type and does not have a format, least of all
one with AM/PM. If you have data as strings in your application, you
should use parameterised statements, so that the strings are converted
in the client with respect to regional settings.
>
Show us the code your having problem with, and we should be able to tell
what you are doing wrong.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

=============== =============== ========
from client ,I have used mm/dd/yyyy hh:mm:ss AM/PM format,same format
is sent to stored procedure,

create procedure sp_checkAvailab ility(@ip_fromT ime
datetime,@ip_to Time datetime)
as
begin
if exists(
select * from conferenceHall c where
(@ip_fromTime< c.fromTime AND @ip_toTime <=c.fromTime)
AND
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
)
print ' Ok'
else
print 'NotOk'


question is , is this comparison works for all types AM/PM format of
@ip_toTime ,@ip_fromTime values
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
because c.fromTime is stored in binary format,
but i dont know how sql server handles @ip_fromTime datetime
values ,since it is input parameter , not yet stored right!

thanks,
Srinivas Reddy Thatiparthy,

[Erland Sommarskog]

Re: Datetime comparison problem

Sreenivas (thatiparthysre enivas@gmail.co m) writes:

from client ,I have used mm/dd/yyyy hh:mm:ss AM/PM format,same format
is sent to stored procedure,

But how did you call the stored procedure? If you let the API convert
the string, you should be alright, provided that this date format
agree with your regional settings. (Then again, in the headers of your
post, I see an IP address that I can locate to Karanataka in India,
why I would expect your regional settings be set to dd/mm/yyyy.)

If you sent an EXEC string (which you shouldn't), conversion happens on
the SQL Server side. The AM/PM part is likely to be understood no matter
the settings, but xx/yy/zzzz could be taken as MM/DD/YYYY or DD/MM/YYYY
depending on the language and date format settings in SQL Server.

create procedure sp_checkAvailab ility(@ip_fromT ime
datetime,@ip_to Time datetime)
as
begin
if exists(
select * from conferenceHall c where
(@ip_fromTime< c.fromTime AND @ip_toTime <=c.fromTime)
AND
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
)
print ' Ok'
else
print 'NotOk'
>
>
question is , is this comparison works for all types AM/PM format of
@ip_toTime ,@ip_fromTime values
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
because c.fromTime is stored in binary format,
but i dont know how sql server handles @ip_fromTime datetime
values ,since it is input parameter , not yet stored right!

I don't know what your code intends to achieve, but it looks funny. Assuming
that @ip_fromTime <= @ip_toTime and conference.from Time <
conferenceHall. toTime, this will always print 'OK'. Given these
assumptions, it's logically impossible that for the same row that
@ip_fromTime can at the same be less than c.fromTime and greater than
toTime.

If the purpose is to see whether there is any rows that do not overlap
with the interval in the parameters, try:

IF NOT EXISTS (SELECT * FROM conferenceHall c
WHERE @ip_fromTime <= c.toTime
AND @ip_toTime >= c.fromTime)
PRINT 'Hall available'
ELSE
PRINT 'Hall booked'

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sreenivas]

Re: Datetime comparison problem

On Nov 2, 3:56 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Sreenivas (thatiparthysre eni...@gmail.co m) writes:

from client ,I have used  mm/dd/yyyy hh:mm:ss AM/PM format,same format
is sent to stored procedure,

>
But how did you call the stored procedure? If you let the API convert
the string, you should be alright, provided that this date format
agree with your regional settings. (Then again, in the headers of your
post, I see an IP address that I can locate to Karanataka in India,
why I would expect your regional settings be set to dd/mm/yyyy.)
>
If you sent an EXEC string (which you shouldn't), conversion happens on
the SQL Server side. The AM/PM part is likely to be understood no matter
the settings, but xx/yy/zzzz could be taken as MM/DD/YYYY or DD/MM/YYYY
depending on the language and date format settings in SQL Server.
>
>
>

create procedure sp_checkAvailab ility(@ip_fromT ime
datetime,@ip_to Time  datetime)
as
begin
if exists(
select * from conferenceHall c  where
(@ip_fromTime< c.fromTime AND @ip_toTime <=c.fromTime)
AND
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
 )
print ' Ok'
else
print 'NotOk'

>

question is , is this comparison works for all types AM/PM format of
@ip_toTime ,@ip_fromTime  values
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
because c.fromTime is stored in binary format,
but i dont know how sql server handles @ip_fromTime datetime
values ,since it is input parameter , not yet stored right!

>
I don't know what your code intends to achieve, but it looks funny. Assuming
that @ip_fromTime <= @ip_toTime and conference.from Time <
conferenceHall. toTime, this will always print 'OK'. Given these
assumptions, it's logically impossible that for the same row that
@ip_fromTime can at the same be less than c.fromTime and greater than
toTime.
>
If the purpose is to see whether there is any rows that do not overlap
with the interval in the parameters, try:
>
  IF NOT EXISTS (SELECT * FROM conferenceHall c
                 WHERE  @ip_fromTime <= c.toTime
                   AND  @ip_toTime >= c.fromTime)
     PRINT 'Hall available'
  ELSE
     PRINT 'Hall booked'
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sorry ! i mistyped it ..

select * from conferenceHall c where
(@ip_fromTime< c.fromTime AND @ip_toTime <=c.fromTime)

It's not AND ..it's OR

OR
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
)

[Sreenivas]

Re: Datetime comparison problem

On Nov 2, 3:56 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Sreenivas (thatiparthysre eni...@gmail.co m) writes:

from client ,I have used  mm/dd/yyyy hh:mm:ss AM/PM format,same format
is sent to stored procedure,

>
But how did you call the stored procedure? If you let the API convert
the string, you should be alright, provided that this date format
agree with your regional settings. (Then again, in the headers of your
post, I see an IP address that I can locate to Karanataka in India,
why I would expect your regional settings be set to dd/mm/yyyy.)
>
If you sent an EXEC string (which you shouldn't), conversion happens on
the SQL Server side. The AM/PM part is likely to be understood no matter
the settings, but xx/yy/zzzz could be taken as MM/DD/YYYY or DD/MM/YYYY
depending on the language and date format settings in SQL Server.
>
>
>

create procedure sp_checkAvailab ility(@ip_fromT ime
datetime,@ip_to Time  datetime)
as
begin
if exists(
select * from conferenceHall c  where
(@ip_fromTime< c.fromTime AND @ip_toTime <=c.fromTime)
AND
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
 )
print ' Ok'
else
print 'NotOk'

>

question is , is this comparison works for all types AM/PM format of
@ip_toTime ,@ip_fromTime  values
(@ip_fromTime>= c.toTime AND @ip_toTime>c.to Time )
because c.fromTime is stored in binary format,
but i dont know how sql server handles @ip_fromTime datetime
values ,since it is input parameter , not yet stored right!

>
I don't know what your code intends to achieve, but it looks funny. Assuming
that @ip_fromTime <= @ip_toTime and conference.from Time <
conferenceHall. toTime, this will always print 'OK'. Given these
assumptions, it's logically impossible that for the same row that
@ip_fromTime can at the same be less than c.fromTime and greater than
toTime.
>
If the purpose is to see whether there is any rows that do not overlap
with the interval in the parameters, try:
>
  IF NOT EXISTS (SELECT * FROM conferenceHall c
                 WHERE  @ip_fromTime <= c.toTime
                   AND  @ip_toTime >= c.fromTime)
     PRINT 'Hall available'
  ELSE
     PRINT 'Hall booked'
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks..