<div>
try it with 
 
blankStartDate = &quot;19400101&quot; 
blankEndDate = &quot;20100606&quot; 

</div>

<div>
No, still get the same error 
 
A.
</div>

<div>
please try this: 

<pre><code class="code-1 nolinks" id="code-122623">blankStartDate = &quot;01/01/1940&quot; -- dd/mm/yyyy, I assume -&gt; 103 in the query below. if it should be mm/dd/yyyy, use 101 instead of 103
blankEndDate = &quot;06/06/2010&quot;
 
set rs = server.createObject(&quot;Adodb.recordset&quot;)
 
MyConn.Execute = &quot;INSERT INTO tblNewsItems (NewItem_Title, NewsItem_LangID, NewsItem_Creator, NewsItem_DateCoLM, NewsItem_StartDate, NewsItem_EndDate, NewItem_METAAuthor, SubSite_ID) VALUES ('&quot; &amp; CleanMe(strNewText) &amp; &quot;', &quot; &amp; Session(&quot;Admin_SiteLang&quot;) &amp; &quot;, '&quot; &amp; Session(&quot;Admin_Name&quot;) &amp; &quot;', '&quot; &amp; now() &amp; &quot;', convert(smalldatetime, '&quot; &amp; blankStartDate &amp; &quot;', 103), convert(smalldatetime, '&quot; &amp; blankEndDate &amp; &quot;', 103) , &quot; &amp; NumDefaultAuthor &amp; &quot;, &quot; &amp; Session(&quot;Subsite_ID&quot;) &amp; &quot; );&quot;
</code></pre>
</div>

<div>
Hello 
 
Why dont you use this avoid any depandancy on server date format ?? 
 
 

<pre><code class="code-1 nolinks" id="code-122625">blankStartDate = &quot;1940/01/01&quot;
blankEndDate = &quot;2010/06/06&quot;
 
'yyyy/mm/dd format
</code></pre>
</div>

<div>
@angelIII 
 
 
Sill get this error.... 
 
----------------------------------------------------------------------------------------------------- 
Microsoft OLE DB Provider for SQL Server error '80040e07' 
 
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. 
 
/grupo_gsi_adip2/VD_Editor/News/DoNewsFiles/DoCreateNews.asp, line 28 
----------------------------------------------------------------------------------------------------- 

</div>

<div>
&gt;Why dont you use this avoid any depandancy on server date format ?? 
actually, even that format can raise errors, depending on login language setting... 
 
only explicit data type conversion is safe.
</div>

<div>
Just in case its something I have regarding the sql setup. i have uploaded a screen shot. 
 
A. 
<a href="https://filedb.experts-exchange.com/incoming/2008/07_w30/43095/1.gif" target="_blank" class="file-inline" title="sql2005 screen shot (23 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>1.gif</a>
</div>

1.gif

<div>
as this works: 
 
select convert(smalldatetime, '01/01/1940', 101), convert(smalldatetime, '06/06/2010', 101) 
 
the problem must be elsewhere. 
any triggers on the table? or other smalldatetime / datetime columns on the table? 

</div>

<div>
the problem might be the now() 
 
To easily test if it is related, replace now() with a hardcoded date.
</div>

<div>
it is always better to provide yyyy/mm/dd format to a database.
</div>

<div>
I still get. 
 
------------------------- 
Microsoft OLE DB Provider for SQL Server error '80040e07' 
Conversion failed when converting character string to smalldatetime data type. 
/grupo_gsi_adip2/VD_Editor/News/DoNewsFiles/DoCreateNews.asp, line 27 
-------------------------- 

<pre><code class="code-1 nolinks" id="code-122640">MyConn.Execute = &quot;INSERT INTO tblNewsItems (NewItem_Title, NewsItem_LangID, NewsItem_Creator, NewsItem_DateCoLM, NewsItem_StartDate, NewsItem_EndDate, NewItem_METAAuthor, SubSite_ID) VALUES ('&quot; &amp; CleanMe(strNewText) &amp; &quot;', &quot; &amp; Session(&quot;Admin_SiteLang&quot;) &amp; &quot;, '&quot; &amp; Session(&quot;Admin_Name&quot;) &amp; &quot;', '&quot; &amp; 2008/07/21 &amp; &quot;', convert(smalldatetime, '&quot; &amp; blankStartDate &amp; &quot;', 103), convert(smalldatetime, '&quot; &amp; blankEndDate &amp; &quot;', 103) , &quot; &amp; NumDefaultAuthor &amp; &quot;, &quot; &amp; Session(&quot;Subsite_ID&quot;) &amp; &quot; );&quot;
</code></pre>
</div>

<div>
@angelIII 
 
There is one other smalldatetime col in the db. it is not being written to. 
 
A.
</div>

<div>
The thing is, if I change the connection string so as to use the original sql2000 it works fine. It has something to do with the sql2005. 
 
A.
</div>

<div>
Sorry, I'm not sure what you mean. i have not done anything else to the db. 
 
A.
</div>

<div>
is there some triggers on the table?
</div>

<div>
Where would i find that. I have open the tblNewsItems in Modify view, btu see no mention of tringers. If Ishould be looking elswhere please tell me. 
 
A.
</div>

<div>
in the Sql Management Studio, when you &quot;+&quot; the table, you have a subfolder &quot;triggers&quot;.
</div>

<div>
no triggers... the folder in empty. 
 
&nbsp;a
</div>

<div>
hey.................. that works!!!! 
 
A.
</div>

<div>
I see the change in date format... Is this a configuration issue on the server. It was unable to convert Now()... 
 
 
I have to pop out. I will be back soon. 
 
many thanks
</div>

<div>
as I said earlier, it is always better to provide dates as yyyy/mm/dd to SQL as there is no chance of misinterpreting a date by the server and you will never get any errors no matter how they are configured. 
 

</div>

<div>
I dare to disagree, but very firmly. 
 
on my local sql server 2005, creating a new connection, and then running the below script, I get 2 errors, which are due to implicit data type conversion influenced by server settings (dateformat and the language can be set per server setting/login): 
 
NEVER : NEVER : NEVER rely on implicit data type conversion 

<pre><code class="code-10 nolinks" id="code-122786">declare @CurrentDate varchar(10)
set @Currentdate = '2008/07/21'
 
select @CurrentDate 
 
select cast(@CurrentDate as smalldatetime) 
 
set language german
 
select cast(@CurrentDate as smalldatetime) 
 
set language english
 
select cast(@CurrentDate as smalldatetime) 
 
set dateformat ymd
 
select cast(@CurrentDate as smalldatetime) 
 
set dateformat dmy
 
select cast(@CurrentDate as smalldatetime) 
 
 
------------ output -----------
 
 
 
----------
2008/07/21
 
(1 row(s) affected)
 
 
-----------------------
2008-07-21 00:00:00
 
(1 row(s) affected)
 
Die Spracheneinstellung wurde auf Deutsch geändert.
 
-----------------------
Msg 296, Level 16, State 3, Line 10
Bei der Konvertierung des char-Datentyps in den smalldatetime-Datentyp liegt der smalldatetime-Wert außerhalb des gültigen Bereichs.
 
Changed language setting to us_english.
 
-----------------------
2008-07-21 00:00:00
 
(1 row(s) affected)
 
 
-----------------------
2008-07-21 00:00:00
 
(1 row(s) affected)
 
 
-----------------------
Msg 296, Level 16, State 3, Line 22
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
</code></pre>
</div>

<div>
Atually, just to update, the following works fine.... There were no problems with the dates as they are formatted below. The only problem was with the '&quot; &amp;&nbsp;NOW() &nbsp;&amp;&quot;'. I think this is formatiing something different than the SQL is expecting, because when I use the SQL native GetDate(), it all works well. 
 
I suspect it has to do with the configuration profile and Locale of the SQL. 
 
 
blankStartDate = &quot;01/01/1900&quot; 
blankEndDate = &quot;06/06/2079&quot; 
 
MyConn.Execute = &quot;INSERT INTO tblNewsItems (NewItem_Title, NewsItem_LangID, NewsItem_Creator, NewsItem_DateCoLM, NewsItem_StartDate, NewsItem_EndDate, NewItem_METAAuthor, SubSite_ID) VALUES ('&quot; &amp;&nbsp;CleanMe(strNewText) &amp;&nbsp;&quot;', &quot;&nbsp;&amp; Session(&quot;Admin_SiteLang&quot;) &amp;&nbsp;&quot;, '&quot; &amp;&nbsp;Session(&quot;Admin_Name&quot;) &amp;&nbsp;&quot;', GetDate(), '&quot; &amp;&nbsp;blankStartDate &amp;&nbsp;&quot;', '&quot; &amp;&nbsp;blankEndDate &amp;&nbsp;&quot;', &quot;&nbsp;&amp; NumDefaultAuthor &amp;&nbsp;&quot;, &quot;&nbsp;&amp; Session(&quot;Subsite_ID&quot;) &amp;&nbsp;&quot; );&quot;
</div>

<div>
Many thanks angelIII I appreciate all. 
 
A.
</div>

<div>
<div class="content wysiwyg-content">
Hi All, 
 
I get the following error when I am attempting to create a record. Thsi worked fine in sql2000, however has a problem with 2005. It seems to be the dates which is causing the problem. 
 
Please help... 
 
A. 
 
 
Microsoft OLE DB Provider for SQL Server error '80040e07' 
 
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. 
 
/grupo_gsi_adip2/VD_Editor/News/DoNewsFiles/DoCreateNews.asp, line 28 
 

<pre><code class="code-1 nolinks" id="code-122618">blankStartDate = &quot;01/01/1940&quot;
blankEndDate = &quot;06/06/2010&quot;
 
set rs = server.createObject(&quot;Adodb.recordset&quot;)
 
MyConn.Execute = &quot;INSERT INTO tblNewsItems (NewItem_Title, NewsItem_LangID, NewsItem_Creator, NewsItem_DateCoLM, NewsItem_StartDate, NewsItem_EndDate, NewItem_METAAuthor, SubSite_ID) VALUES ('&quot; &amp; CleanMe(strNewText) &amp; &quot;', &quot; &amp; Session(&quot;Admin_SiteLang&quot;) &amp; &quot;, '&quot; &amp; Session(&quot;Admin_Name&quot;) &amp; &quot;', '&quot; &amp; now() &amp; &quot;', '&quot; &amp; blankStartDate &amp; &quot;', '&quot; &amp; blankEndDate &amp; &quot;', &quot; &amp; NumDefaultAuthor &amp; &quot;, &quot; &amp; Session(&quot;Subsite_ID&quot;) &amp; &quot; );&quot;
</code></pre>
</div>
</div>

Hi All,

I get the following error when I am attempting to create a record. Thsi worked fine in sql2000, however has a problem with 2005. It seems to be the dates which is causing the problem.

Please help...

A.


Microsoft OLE DB Provider for SQL Server error '80040e07' 

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. 

/grupo_gsi_adip2/VD_Editor/News/DoNewsFiles/DoCreateNews.asp, line 28 


Insert  smalldatetime value

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.