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 10 8157
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
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
Why stiil ansering this kind of questions ? Already in this post 1891 times.
Please check www.aspfaq.com
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
> 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
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
> 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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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) -...
|
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.
...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
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,...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |