Date Problems - ASP/SQL

[Fawke101]

Hi there,

I have a date field in SQL server - that holds dates as DD/MM/YYYY format
(GB).

Now, i have an ASP application that Adds/Edits records in this table; and i
am having real problems with the date field in the ADD and EDIT (update)
part for this app.

Basically, i receive and Error as below:

****
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[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.
/add.asp, line 105
****

This occurs when i try to input a date in the format of DD/MM/YYYY through
an ASP Form. When i try it as MM/DD/YYYY it works great, I need it as GB not
US!

Any ideas how i can fix this?
I have already entered this at the top of the ADD/Edit page:

<%session.LCID= 2057%> (just a stab in the dark)

Any ideas much appreciated

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

[Don Grover]

Re: Date Problems - ASP/SQL

Put the date in as YYYY/MM/DD .

This is a function i use when passing in dd/mm/yyyy dates and works well.
Use like this update tblMisc set mydatefield = convDate('dd/mm/yyyy')

<%
'This function recieves a date from text string in format dd/mm/yy or
dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a
datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a
CCYY year
'---------
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'---------
'pass a date as dd/mm/yy
Function convDate(theDat e)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2 )) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>



"Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:%23MGOI9MO EHA.1276@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> Hi there,
>
> I have a date field in SQL server - that holds dates as DD/MM/YYYY format
> (GB).
>
> Now, i have an ASP application that Adds/Edits records in this table; and[/color]
i[color=blue]
> am having real problems with the date field in the ADD and EDIT (update)
> part for this app.
>
> Basically, i receive and Error as below:
>
> ****
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char[/color]
data[color=blue]
> type to a datetime data type resulted in an out-of-range datetime value.
> /add.asp, line 105
> ****
>
> This occurs when i try to input a date in the format of DD/MM/YYYY through
> an ASP Form. When i try it as MM/DD/YYYY it works great, I need it as GB[/color]
not[color=blue]
> US!
>
> Any ideas how i can fix this?
> I have already entered this at the top of the ADD/Edit page:
>
> <%session.LCID= 2057%> (just a stab in the dark)
>
> Any ideas much appreciated
>
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
>
>[/color]

[Fawke101]

Re: Date Problems - ASP/SQL

Pucker.... thats great, cheers for that... works a treat

Just one thing - how come this is commented out? -
[color=blue]
> '---------
> 'sValues = " NULLIF('" & convdate(sDate) & "','')"
> '---------[/color]

What does this part do exactly?
--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

[Maarten]

Re: Date Problems - ASP/SQL

Why stiil ansering this kind of questions ? Already in this post 1891 times.
Please check www.aspfaq.com

[Fawke101]

Re: Date Problems - ASP/SQL

relax man, im sorry........

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Maarten" <nobody@spam.co m> wrote in message
news:QXIoc.1055 58$4k7.6301725@ phobos.telenet-ops.be...[color=blue]
> Why stiil ansering this kind of questions ? Already in this post 1891[/color]
times.[color=blue]
> Please check www.aspfaq.com
>
>[/color]

[Aaron Bertrand - MVP]

Re: Date Problems - ASP/SQL

> I have a date field in SQL server - that holds dates as DD/MM/YYYY format[color=blue]
> (GB).[/color]

(a) SQL Server has columns, not fields.

(b) no, a DATETIME column does NOT store DD/MM/YYYY format. Maybe that's
what Enterprise Manager shows you, based on your regional settings. But it
is NOT what's stored in the database.

(c) the safest format is ISO standard: YYYYMMDD. YYYY/MM/DD could still
fail in certain scenarios, e.g. run this script on a scratch system, and
you'll see that YYYY/MM/DD will fail.



PRINT '--- FRENCH ---'
SET LANGUAGE FRENCH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO
PRINT '--- US ENGLISH ---'
SET LANGUAGE ENGLISH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO
PRINT '--- UK ENGLISH ---'
SET LANGUAGE BRITISH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO


--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/[color=blue]
>
> Now, i have an ASP application that Adds/Edits records in this table; and[/color]
i[color=blue]
> am having real problems with the date field in the ADD and EDIT (update)
> part for this app.
>
> Basically, i receive and Error as below:
>
> ****
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char[/color]
data[color=blue]
> type to a datetime data type resulted in an out-of-range datetime value.
> /add.asp, line 105
> ****
>
> This occurs when i try to input a date in the format of DD/MM/YYYY through
> an ASP Form. When i try it as MM/DD/YYYY it works great, I need it as GB[/color]
not[color=blue]
> US!
>
> Any ideas how i can fix this?
> I have already entered this at the top of the ADD/Edit page:
>
> <%session.LCID= 2057%> (just a stab in the dark)
>
> Any ideas much appreciated
>
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
>
>[/color]

[Fawke101]

Re: Date Problems - ASP/SQL

Sorry, yea, thats what shows in Enterprise Manager.

As long as thats how its displayed to the "user" is all that matters to us.
The ASP returns the date in DDMMYYYY to the page and it also enters the date
in the DB (or at least appears to) as DDMMYYYY.
Its all cosmetic but its not a moster application by any means, and this is
OK as long as it works!

The Views work great using the DDMMYYYY format, or that they "appear" to
use.

Thanks though, well worth knowing what its doing behind the scenes

--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Aaron Bertrand - MVP" <aaron@TRASHasp faq.com> wrote in message
news:uWAZznPOEH A.2468@TK2MSFTN GP11.phx.gbl...[color=blue][color=green]
> > I have a date field in SQL server - that holds dates as DD/MM/YYYY[/color][/color]
format[color=blue][color=green]
> > (GB).[/color]
>
> (a) SQL Server has columns, not fields.
>
> (b) no, a DATETIME column does NOT store DD/MM/YYYY format. Maybe that's
> what Enterprise Manager shows you, based on your regional settings. But[/color]
it[color=blue]
> is NOT what's stored in the database.
>
> (c) the safest format is ISO standard: YYYYMMDD. YYYY/MM/DD could still
> fail in certain scenarios, e.g. run this script on a scratch system, and
> you'll see that YYYY/MM/DD will fail.
>
>
>
> PRINT '--- FRENCH ---'
> SET LANGUAGE FRENCH
> DECLARE @dt SMALLDATETIME
> SET @dt = '2004/11/13'
> PRINT @dt
> GO
> DECLARE @dt SMALLDATETIME
> SET @dt = '20041113'
> PRINT @dt
> GO
> PRINT '--- US ENGLISH ---'
> SET LANGUAGE ENGLISH
> DECLARE @dt SMALLDATETIME
> SET @dt = '2004/11/13'
> PRINT @dt
> GO
> DECLARE @dt SMALLDATETIME
> SET @dt = '20041113'
> PRINT @dt
> GO
> PRINT '--- UK ENGLISH ---'
> SET LANGUAGE BRITISH
> DECLARE @dt SMALLDATETIME
> SET @dt = '2004/11/13'
> PRINT @dt
> GO
> DECLARE @dt SMALLDATETIME
> SET @dt = '20041113'
> PRINT @dt
> GO
>
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/[color=green]
> >
> > Now, i have an ASP application that Adds/Edits records in this table;[/color][/color]
and[color=blue]
> i[color=green]
> > am having real problems with the date field in the ADD and EDIT (update)
> > part for this app.
> >
> > Basically, i receive and Error as below:
> >
> > ****
> > Error Type:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char[/color]
> data[color=green]
> > type to a datetime data type resulted in an out-of-range datetime value.
> > /add.asp, line 105
> > ****
> >
> > This occurs when i try to input a date in the format of DD/MM/YYYY[/color][/color]
through[color=blue][color=green]
> > an ASP Form. When i try it as MM/DD/YYYY it works great, I need it as GB[/color]
> not[color=green]
> > US!
> >
> > Any ideas how i can fix this?
> > I have already entered this at the top of the ADD/Edit page:
> >
> > <%session.LCID= 2057%> (just a stab in the dark)
> >
> > Any ideas much appreciated
> >
> > --
> > Thanks in advance
> >
> > Fawke
> >
> > Please remove ANTI and SPAM
> > from my email address before emailing me.
> >
> > www.bradflack.com
> >
> >[/color]
>
>[/color]

[Aaron Bertrand - MVP]

Re: Date Problems - ASP/SQL

> The ASP returns the date in DDMMYYYY to the page and it also enters the
date[color=blue]
> in the DB (or at least appears to) as DDMMYYYY.
> Its all cosmetic but its not a moster application by any means, and this[/color]
is[color=blue]
> OK as long as it works!
>
> The Views work great using the DDMMYYYY format, or that they "appear" to
> use.[/color]

Display is one thing. I *STRONGLY, STRONGLY* recommend you change the
format that you pass into the database (not necessarily what the user
enters, but between entry and passing) to a standard format that will not
fail. It's amazing how easy it is for some user to enter SET LANGUAGE
ENGLISH mistakenly. Or change regional settings on the machine. Or what
happens when you move your SQL Server to a different machine that isn't set
up properly. Are you prepared to re-write all of your code during crisis
time, or do you want to prepare for it in advance and sleep better at night?

http://www.aspfaq.com/2260

http://www.aspfaq.com/2313

http://www.karaszi.com/SQLServer/info_datetime.asp

[Fawke101]

Re: Date Problems - ASP/SQL

OK, at the risk of sounding daft....

How could i incorporate the code needed for YYYYMMDD (behind the scenes)
into the current application.
Basically i need to have the Text box in ASP display the date as DD/MM/YYYY
(and users to enter the date as DD/MM/YYYY) and Enterprise manager
(presumably it will anyway, depending on Locale (UK)) whilst passing the
Data as what you recommend.

Thanks for this, i would have gone on regardless had you not of pointed this
out....
I presume you dont mind helping me implement this?

Thanks so much
--
Thanks in advance

Fawke

Please remove ANTI and SPAM
from my email address before emailing me.

https://www.bradflack.com

"Aaron Bertrand - MVP" <aaron@TRASHasp faq.com> wrote in message
news:ORoLS6POEH A.1340@TK2MSFTN GP12.phx.gbl...[color=blue][color=green]
> > The ASP returns the date in DDMMYYYY to the page and it also enters the[/color]
> date[color=green]
> > in the DB (or at least appears to) as DDMMYYYY.
> > Its all cosmetic but its not a moster application by any means, and this[/color]
> is[color=green]
> > OK as long as it works!
> >
> > The Views work great using the DDMMYYYY format, or that they "appear" to
> > use.[/color]
>
> Display is one thing. I *STRONGLY, STRONGLY* recommend you change the
> format that you pass into the database (not necessarily what the user
> enters, but between entry and passing) to a standard format that will not
> fail. It's amazing how easy it is for some user to enter SET LANGUAGE
> ENGLISH mistakenly. Or change regional settings on the machine. Or what
> happens when you move your SQL Server to a different machine that isn't[/color]
set[color=blue]
> up properly. Are you prepared to re-write all of your code during crisis
> time, or do you want to prepare for it in advance and sleep better at[/color]
night?[color=blue]
>
> http://www.aspfaq.com/2260
> http://www.aspfaq.com/2313
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>[/color]

[Aaron Bertrand - MVP]

Re: Date Problems - ASP/SQL

So, you pass that date to an ASP page, which passes the data to a database,
right?

Then, when you bring the string in from the previous page, do not assume it
is a date... since your locale could change at any time and this will mess
up the order (dd/mm vs. mm/dd). Instead, do this:

dt = Request.Form("d t") ' dd/mm/yyyy format
dtParts = split(dt, "/")
dt = dtParts(2) & _
right("00" & dtParts(1), 2) & _
right("00" & dtParts(0), 2)
response.write dt

The other side of this is that it assumes your users will always enter
DD/MM/YYYY. It is difficult to account for mistakes that are made on a
logical/conceptual layer. You will never be able to protect a user from
mistakes that are legal but not what they intended, e.g. 05/03/2004 vs.
03/05/2004...

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:OE4PxtQOEH A.2100@TK2MSFTN GP11.phx.gbl...[color=blue]
> OK, at the risk of sounding daft....
>
> How could i incorporate the code needed for YYYYMMDD (behind the scenes)
> into the current application.
> Basically i need to have the Text box in ASP display the date as[/color]
DD/MM/YYYY[color=blue]
> (and users to enter the date as DD/MM/YYYY) and Enterprise manager
> (presumably it will anyway, depending on Locale (UK)) whilst passing the
> Data as what you recommend.
>
> Thanks for this, i would have gone on regardless had you not of pointed[/color]
this[color=blue]
> out....
> I presume you dont mind helping me implement this?
>
> Thanks so much
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
> "Aaron Bertrand - MVP" <aaron@TRASHasp faq.com> wrote in message
> news:ORoLS6POEH A.1340@TK2MSFTN GP12.phx.gbl...[color=green][color=darkred]
> > > The ASP returns the date in DDMMYYYY to the page and it also enters[/color][/color][/color]
the[color=blue][color=green]
> > date[color=darkred]
> > > in the DB (or at least appears to) as DDMMYYYY.
> > > Its all cosmetic but its not a moster application by any means, and[/color][/color][/color]
this[color=blue][color=green]
> > is[color=darkred]
> > > OK as long as it works!
> > >
> > > The Views work great using the DDMMYYYY format, or that they "appear"[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > use.[/color]
> >
> > Display is one thing. I *STRONGLY, STRONGLY* recommend you change the
> > format that you pass into the database (not necessarily what the user
> > enters, but between entry and passing) to a standard format that will[/color][/color]
not[color=blue][color=green]
> > fail. It's amazing how easy it is for some user to enter SET LANGUAGE
> > ENGLISH mistakenly. Or change regional settings on the machine. Or[/color][/color]
what[color=blue][color=green]
> > happens when you move your SQL Server to a different machine that isn't[/color]
> set[color=green]
> > up properly. Are you prepared to re-write all of your code during[/color][/color]
crisis[color=blue][color=green]
> > time, or do you want to prepare for it in advance and sleep better at[/color]
> night?[color=green]
> >
> > http://www.aspfaq.com/2260
> > http://www.aspfaq.com/2313
> > http://www.karaszi.com/SQLServer/info_datetime.asp
> >
> >[/color]
>
>[/color]

[Don Grover]

Re: Date Problems - ASP/SQL

Just a mental reminder what the sql string composition is like.
Nullif checks for nulk and puts second parameter in if needed
Don

"Fawke101" <guy@ANTIbradfl ack.SPAMcom> wrote in message
news:e7GBUSNOEH A.2876@TK2MSFTN GP09.phx.gbl...[color=blue]
> Pucker.... thats great, cheers for that... works a treat
>
> Just one thing - how come this is commented out? -
>[color=green]
> > '---------
> > 'sValues = " NULLIF('" & convdate(sDate) & "','')"
> > '---------[/color]
>
> What does this part do exactly?
> --
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
>
>[/color]