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 8166
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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP11.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.co m> 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***@TRASHasp faq.com> wrote in message
news:uW******** ******@TK2MSFTN GP11.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***@TRASHasp faq.com> wrote in message
news:OR******** ******@TK2MSFTN GP12.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("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" <gu*@ANTIbradfl ack.SPAMcom> wrote in message
news:OE******** ******@TK2MSFTN GP11.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***@TRASHasp faq.com> wrote in message news:OR******** ******@TK2MSFTN GP12.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
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 depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.
|
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) -
DAYS(PROBLEMS.CLOSE_DATE)) = 365 AND PROBLEMS.CLOSE_DATE IS NOT NULL
But this doesn't:
db2 SELECT DISTINCT PROBLEM_OBJECTS.PROBLEM_ID FROM...
|
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.
This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report...
|
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 into an access
database to manipulate it. In the table I have:
FIRSTNAME | LASTNAME | BADGE# | DATE | TIME
The problem is that the...
|
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 so many problems ....
first of all i try access with uk settings in date/time in field but
access changes date after 09/11 to 11/09 i don't know...
| |
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 same format
as what has been set in my local machine. For e.g. if today is 21st
February 2007 & the time is 10:45:32 AM, then this record gets...
|
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 recognizable date format, I've done the following:
Query 1: References Source Table; Isolates Year, Day; creates MMDD field
Acc Open Year: Left(,4)...
|
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
("d")+7, date("Y")));
else if ($close_date == 1)
|
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 22:02:51? (24 hour day is fine)
How about the date after 02/28/04 is 02/29/04, or the date after 09/30/08 is
10/01/08?
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |