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.. me out!
Thanks in advance..

Sreenivas (thatiparthysreenivas@gmail.com) 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.. 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@sommarskog.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

On Nov 1, 6:34 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Sreenivas (thatiparthysreeni...@gmail.com) 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.. 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...@sommarskog.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_checkAvailability(@ip_fromTime
datetime,@ip_toTime 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.toTime )
)
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.toTime )
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,

Sreenivas (thatiparthysreenivas@gmail.com) 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_checkAvailability(@ip_fromTime
> datetime,@ip_toTime 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.toTime )
> )
> 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.toTime )
> 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.fromTime <
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@sommarskog.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

On Nov 2, 3:56pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Sreenivas (thatiparthysreeni...@gmail.com) 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_checkAvailability(@ip_fromTime
> > datetime,@ip_toTime 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.toTime )
> > )
> > 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.toTime )
> > 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.fromTime <
> 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...@sommarskog.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.toTime )
> > )

On Nov 2, 3:56pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Sreenivas (thatiparthysreeni...@gmail.com) 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_checkAvailability(@ip_fromTime
> > datetime,@ip_toTime 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.toTime )
> > )
> > 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.toTime )
> > 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.fromTime <
> 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...@sommarskog.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..