Re: Searching on DATETIME Fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 08:42:14 -0600

> I have seen that before, but my question is, why would passing 19/03/2004
> 00:00:00 to a field of type datetime, which does contain values in this
> format, fail?

Did you read Tibor's article? Datetime is not stored in any displayable
format since it is stored internally as numeric data. When you pass the
date as a string, you are at the mercy of the connection settings for
interpretation. Format 'yyyymmyy' is safest in this regard.

Another option your might consider is to pass the value as a datetime
command parameter instead of building a SQL string. IMHO, this is the best
approach for dates as well as other data types. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Keith" <@.> wrote in message news:eesLa4bDEHA.3584@TK2MSFTNGP11.phx.gbl...
> I have seen that before, but my question is, why would passing 19/03/2004
> 00:00:00 to a field of type datetime, which does contain values in this
> format, fail?
>
> Surely passing the value is all that is required regardless of the
datatype
> as long as it matches the data contained in the field?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:eemPg0bDEHA.688@tk2msftngp13.phx.gbl...
> > All you need at:
> >
> > http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Keith" <@.> wrote in message
> news:uqwYixbDEHA.1544@TK2MSFTNGP09.phx.gbl...
> > > I am fairly new to SQL so sorry if this is a really dumb question.
> > >
> > > I have a small (still) SQL database, which I am trying to query from
an
> > ASP
> > > page.
> > >
> > > The field I am querying is of DATETIME data type, and is populated
> > > automatically using the GetDate() function as a default value.
> > >
> > > When I try and search on this field, using a date/time in the format
> > > dd/mm/yyyy hh:mm:ss as the search criteria, it fails with the
following
> > > error:
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a
char
> > data
> > > type to a datetime data type resulted in an out-of-range datetime
value.
> > >
> > > Can anyone tell me why this is?  Surely if I am passing a value in the
> > > correct format for the data stored in the field it should work?  Or am
I
> > > missing something.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Oh... no....
    ... select castas datetime) ... Obviously it would be better to use the "standard" format. ... Pro SQL Server 2000 Database Design ... >> data type of the date_variable in stored proc have been set to char ...
    (microsoft.public.sqlserver.programming)
  • Re: Dates
    ... I have never ever had any problems with a date in ISO 8601 format. ... The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ... System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +260 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Insert DateTime Value In DB Table?
    ... Is the date format whch you are trying to insert the same ... The data type of the column named "DOB" in the ... DB table is "datetime" which means SQL ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Date Conversion!
    ... The DOB is the querystring params and you are parsing it as a DATETIME ... The format is just setting it as a en-gb culture ... >>> Thus in my stored procedure i recieve a can not convert data type ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: How to Validate a Date Filed.
    ... I'm writing, and the fourth test is just getting fancy; ... IsValid As Boolean = True) As DateTime ... > You can validate a date using Regular Expressions. ... > objects and setting culture to a country that uses the date format you ...
    (microsoft.public.dotnet.framework.aspnet)