473,387 Members | 1,597 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Date Problems - ASP/SQL

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
Jul 19 '05 #1
10 8151
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" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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

Jul 19 '05 #2
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
Jul 19 '05 #3
Why stiil ansering this kind of questions ? Already in this post 1891 times.
Please check www.aspfaq.com
Jul 19 '05 #4
relax man, im sorry........

--
Thanks in advance

Fawke

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

www.bradflack.com
"Maarten" <no****@spam.com> wrote in message
news:QX**********************@phobos.telenet-ops.be...
Why stiil ansering this kind of questions ? Already in this post 1891 times. Please check www.aspfaq.com

Jul 19 '05 #5
> 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

Jul 19 '05 #6
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
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:uW**************@TK2MSFTNGP11.phx.gbl...
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


Jul 19 '05 #7
> 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
Jul 19 '05 #8
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" <aa***@TRASHaspfaq.com> wrote in message
news:OR**************@TK2MSFTNGP12.phx.gbl...
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

Jul 19 '05 #9
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/


"Fawke101" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...
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" <aa***@TRASHaspfaq.com> wrote in message
news:OR**************@TK2MSFTNGP12.phx.gbl...
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


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

"Fawke101" <gu*@ANTIbradflack.SPAMcom> wrote in message
news:e7**************@TK2MSFTNGP09.phx.gbl...
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

Jul 19 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: carverk | last post by:
Hello All I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records...
5
by: Corky | last post by:
This works: db2 SELECT DISTINCT PROBLEM_OBJECTS.PROBLEM_ID FROM PROBLEM_OBJECTS INNER JOIN PROBLEMS ON PROBLEM_OBJECTS.PROBLEM_ID = PROBLEMS.PROBLEM_ID WHERE INTEGER(DAYS(CURRENT DATE) -...
13
by: Deano | last post by:
Hi, I generate a report using two dates (From and To). I notice if I enter 01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January rather than 1st October as I intended. ...
2
by: Riegn Man | last post by:
I have a problem with access and our time clocks. We have time clocks that put out a .log file with the badge swipes for everybody. There is one .log file for each day. I am pulling that data...
2
by: hardik | last post by:
hi friends, i am really surprized the way access behaves in date fields i mean it's all ok when you have us time zone or us servers but if you have diffrent timezone like uk then access creates...
3
by: rn5a | last post by:
In my local computer, date has been set in this format - dd/MM/yyyy. When I insert records in a MS-Access DB table using ASP.NET, then the records get inserted in the Access DB table exactly in the...
2
by: thewilldog | last post by:
Hello, I've reviewed the archives here to address the issue, but I'm still running into problems. I've got a table field populated with the record date in text "YYYYMMDD" To convert it into a...
3
by: JJ | last post by:
Here's the code. $link="http://xbox360cheat.org"; $close_date=$_POST; #last content change check if ($close_date == 0) $close_date = date("Y-m-d H:m:s", mktime(12, 0, 0, date("m"), date...
16
by: W. eWatson | last post by:
Are there some date and time comparison functions that would compare, say, Is 10/05/05 later than 09/22/02? (or 02/09/22 format, yy/mm/dd) Is 02/11/07 the same as 02/11/07? Is 14:05:18 after...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.