DoDateTime YYYY/MM/DD

Report · Apr 23, 2007

Hi all, i would be grateful for any help with this little date input query. i'm developing in ASP against an MS SQL database, I'm also using the Expert Calendar extension from an extension from Kaosweaver which on record insert formats the date in a YYYY/MM/DD format which is great and works excellently.

The problem that i have, is that i have an update record page, which returns the date entered to a text area, however it returns the date in a DD/MM/YYYY format, which unfortunatly causes a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." Error on insert unless the user changes the date (as my database is expecting the value in YYYY/MM/DD format)

I've tried to change the date format using Dreeamweavers pre-built DoDate functions, however can not find one that returns the date in the following format YYYY/MM/DD

Please can you advise as to how to change this, i'm guessing that it has something to do with the values 2, 2070 listed below?

<%= DoDateTime((Recordset1.Fields.Item("PCCCalendarDate").Value), 2, 2070) %>

Thanks for your help

Report · Apr 23, 2007

Are you using a stored procedure to perform the update?

If so, try putting 'set dateformat ymd' (without the quotes) at the top of
your stored proc SQL underneath AS. Failing that, look up the various FORMAT
options in Books Online. Really though, you should store dates in native
style and do any formatting in your asp code. Dates and SQL Server can be a
pain in the ass though.

Pat.

"GTN1707" <webforumsuser@macromedia.com> wrote in message
news:f0iajv$pkm$1@forums.macromedia.com...
> Hi all, i would be grateful for any help with this little date input
> query. i'm
> developing in ASP against an MS SQL database, I'm also using the Expert
> Calendar extension from an extension from Kaosweaver which on record
> insert
> formats the date in a YYYY/MM/DD format which is great and works
> excellently.
>
> The problem that i have, is that i have an update record page, which
> returns
> the date entered to a text area, however it returns the date in a
> DD/MM/YYYY
> format, which unfortunatly causes a "The conversion of a char data type to
> a
> datetime data type resulted in an out-of-range datetime value." Error on
> insert
> unless the user changes the date (as my database is expecting the value in
> YYYY/MM/DD format)
>
> I've tried to change the date format using Dreeamweavers pre-built DoDate
> functions, however can not find one that returns the date in the following
> format YYYY/MM/DD
>
> Please can you advise as to how to change this, i'm guessing that it has
> something to do with the values 2, 2070 listed below?
>
> <%= DoDateTime((Recordset1.Fields.Item("PCCCalendarDate").Value), 2, 2070)
> %>
>
> Thanks for your help
>

Report · Apr 23, 2007

Hi Pat sadly not using a Stored Procedure - any ideas?

Report · Apr 23, 2007

SQL Server magazine has had several articles on DATE/TIME recently

http://www.sqlmag.com/

On Mon, 23 Apr 2007 12:59:43 +0000 (UTC), "GTN1707"
<webforumsuser@macromedia.com> wrote:

>Hi all, i would be grateful for any help with this little date input query. i'm
>developing in ASP against an MS SQL database, I'm also using the Expert
>Calendar extension from an extension from Kaosweaver which on record insert
>formats the date in a YYYY/MM/DD format which is great and works excellently.
>
> The problem that i have, is that i have an update record page, which returns
>the date entered to a text area, however it returns the date in a DD/MM/YYYY
>format, which unfortunatly causes a "The conversion of a char data type to a
>datetime data type resulted in an out-of-range datetime value." Error on insert
>unless the user changes the date (as my database is expecting the value in
>YYYY/MM/DD format)
>
> I've tried to change the date format using Dreeamweavers pre-built DoDate
>functions, however can not find one that returns the date in the following
>format YYYY/MM/DD
>
> Please can you advise as to how to change this, i'm guessing that it has
>something to do with the values 2, 2070 listed below?
>
> <%= DoDateTime((Recordset1.Fields.Item("PCCCalendarDate").Value), 2, 2070) %>
>
> Thanks for your help

Bruce McKee
mm-help@phoenix-systems.com
For additional info on this, please send email to the
above addess.