I want to convert from text to date. What am I doing wrong.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I want to convert from text to date. What am I doing wrong.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-28 : 10:06:18

Gunnar Hansen writes "The error message I get is the following:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

And my ASP code is the following:

Option Explicit

Dim AdminConn, AdminRS
Dim strSQL

Set AdminConn = Server.CreateObject("ADODB.Connection")
AdminConn.Open AdminConnect

strSQL = "INSERT INTO t_skra (dato,event,text,show) VALUES ('" & Request.Form("dato") & "','" & Request.Form("event") & "','" & Request.Form("text") & "','" & Request.Form("show") & "')"

AdminConn.Execute(strSQL)
AdminConn.Close

Response.Write "Ordan"

Thanks for your help!"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-28 : 10:41:33

quote:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Nothing wrong with your code here. The error message explains the problem well enough - you're inserting a value into a smalldatetime column which falls outside the defined range of dates that a smalldatetime column can hold.

If I ran:

declare @x smalldatetime
set @x = '1492-01-01'

I would get the same error. Check BOL for a description of the allowable range for a smalldatetime ( or change the column definition to be a datetime ).

Jonathan Boott, MCDBA

izaltsman
A custom title

1139 Posts

Posted - 2002-06-28 : 10:45:15

And if your date is within the acceptable range you have probably hit a date formatting issue.
The reason you are getting an error when you are trying to insert a date could be that your web page sends the date in a format different from the one SQL Server is expecting. You can use SQL's CONVERT function (see BOL for syntax) to tell SQL Server which format it needs to expect, or you could use ASP string functions to parse and re-format your date to an ISO format (yyyymmdd), which is always correctly recognized by SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources