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.

www.bradflack.com

 

[Don Grover]

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(theDate)
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]

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

Just one thing - how come this is commented out? -

'---------
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'---------

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

 

[Maarten]

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

 

[Fawke101]

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

--
Thanks in advance

Fawke

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

www.bradflack.com

 

[Aaron Bertrand - MVP]

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

(GB).

(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/

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.

www.bradflack.com

 

[Fawke101]

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.

www.bradflack.com

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

(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/

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.

www.bradflack.com

 

[Aaron Bertrand - MVP]

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.

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]

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.

www.bradflack.com

 

[Aaron Bertrand - MVP]

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("dt") ' 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/

 

[Don Grover]

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