Re: Oh... no....

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/16/04 

Date: Mon, 15 Mar 2004 23:34:30 -0600

If you are forced to use this format (for some reason or another) you could
use replace to eliminate the dashes and pass it as varchar.

select cast('1999-10-20' as datetime)

select cast(replace('1999-10-20','-','') as datetime)

The only concernt I see is that the first one works fine for me, and I
cannot imagine why it wouldn't for you. It gives you an error when you run
the first command?

Obviously it would be better to use the "standard" format. 

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:%235s5ZMxCEHA.3348@TK2MSFTNGP11.phx.gbl...
> Use YYYYMMDD.  Any other format is vulnerable to locale settings, regional
> issues, SET LANGUAGE, SET DATEFORMAT, etc.  See
> http://www.karaszi.com/sqlserver/info_datetime.asp for a primer...
>
> -- 
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Utada P.W. SIU" <wing0508@hotmail.com> wrote in message
> news:uGlQa9wCEHA.1544@TK2MSFTNGP09.phx.gbl...
> > I am really need your help...
> >
> > How to pass a date with format "yyyy-mm-dd" to a stored procedure?
> >
> > I have tried many method
> >
> > 1.
> > the data type of the date_variable in stored proc have been set to
> > "DATETIME"
> > using parameter object of ADODB.command to password a string with a type
> > adDBTimeStamp
> > not work
> >
> > 2.
> > data type of the date_variable in stored proc have been set to char(10)
> > pass a char with type adChar
> > and using weather CAST or CONVERT to change the char to datetime
> >
> > not work...
> >
> > return following error
> >
> > Microsoft OLE DB Provider for SQL Server error '80040e07'
> >
> > Syntax error converting datetime from character string.
> >
> >
> >
> >
>
>

Relevant Pages

  • 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: Date Formats
    ... tables of SQL Server. ... I had problem in exporting these fields into Oracle. ... > I presume that the problem is that Oracle doesn't interpret the datetime values from SQL server ... > a format, hence trying to enforce a format for that datatype is meaningless. ...
    (microsoft.public.sqlserver.programming)
  • Re: Using CASE .. WHEN to have dynamic sort
    ... > I'm trying to create a Stored Procedure that returns a recordset, ... > The conversion of a char data type to a datetime data type resulted in ... > an out-of-range datetime value. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: convert(datetime, datefld, 101) error
    ... datefld is a varchar field in a sql server 2000 table, ... >already in a SQL Server table? ... >format that is completely unambiguous. ... stored as datetime, ...
    (microsoft.public.sqlserver.programming)
  • Re: Convert help needed desperately
    ... overflow error converting expression to data type datetime. ... Is my sql server hosed???? ... > the timestamp data type. ...
    (microsoft.public.sqlserver.programming)