Re: Searching on DATETIME Fields

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 10:00:42 -0500

SQL can accept Strings and convert to dates, the ISO format is always
accepted. Other input formats are accepted based on the connections
DATEFORMAT property ( See Books on line SET DATEFORMAT)

So First try quoting the string, then ensure that the format of the date
string is appropriate. 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"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: Moving a VBA function to SQL server
    ... SQL is a set-based language. ... "String Functions " in Books Online. ... This will produce a job header formatted like this: ... The format he specifies is stored in table 'Settings' - field 'JobHdrMask' ...
    (comp.databases.ms-access)
  • Re: ADP ignoring my format code
    ... we're talking VBA, not SQL, then all we need is Month. ... what the original poster wanted was a string ... representation of a complete date in US format. ... "Vadim Rapp" wrote in message ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Storing Doubles in SQL
    ... Also it's pointless to format the value before storing it. ... Dave Patrick ....Please no email replies - reply in newsgroup. ... string and format as in: ... >I can successfully read and write from/to sql using excel macros. ...
    (microsoft.public.excel.programming)
  • Re: simple query not so simple
    ... If that is truly your SQL statement, you have just asked to find all ... In Oracle all dates contain 'century, year, month, date, hour, minute, ... string - if you can read it, it's a string, not a date - so use string ... either implicitly (if your string is in the session's date format) ...
    (comp.databases.oracle.misc)
  • Re: Searching on DATETIME Fields
    ... Because you are using an ambiguous date format. ... Please use a standard format like YYYYMMDD, so SQL Server ... >>> The field I am querying is of DATETIME data type, ...
    (microsoft.public.sqlserver.server)