Re: Datetime comparison problem

On Nov 1, 6:34 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Sreenivas (thatiparthysreeni...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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/prodinfo/previousversions/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,
.

Relevant Pages

  • Re: Using variables in Dynamic SQL
    ... >converting datetime from character string." ... the CONVERT function in this piece of the code to format the date back to ... If you print the resulting string in @sql, you'll see that you have some ... DECLARE @StartDate datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: Datetime comparison problem
    ... does datetime works fine for AM/PM format also? ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • RE: SqlCeEngine.CreateDataBase() - LCID - Datepart format problem
    ... Background info on why insert in your format doesn't work: ... datetime as string, SQL CE does an implicit convert from string to ... datetime formats with their style IDs are listed in CONVERT function in SQL ...
    (microsoft.public.sqlserver.ce)
  • Re: VB & SAL Date/Time Problem?
    ... you don't need to be concerned with datetime ... SQL Server MVP ... > I've written an ASP app that requires users to login - normal stuff. ... > So I believe that what I need to do is to convert the date time format ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting regional settings specific data into sql server
    ... All EU countries withouth the two English speaking use the format dd-MM-yy ... By using the parameters, while the program is in the right culture setting, ... than all those formats results using SQLparameters in the way the SQL server ...
    (microsoft.public.dotnet.framework.adonet)