DateTime unable to save in datetime field of SQL database

 Hi all, having a little problem with saving dates to sql database

I've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up 

 Error "

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

I've tried researching it but not been able to find something similar. 

 

Heres the code: 

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')";

 Any help would be much appreciated

 

0
dci_burnside
3/14/2007 8:18:07 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
826 Views

Similar Articles

[PageSpeed] 47

If you are using SQL Server, change the statement to

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "',GetDate())

If you are using Access then use this: 

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "',Date())

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
3/14/2007 9:18:47 PM

Sorry, my fault i should have said, i'm coding in c sharp, heres the expanded function

void AddToQuizIndex(String userName, String quizName, String description, String question_xml)

{

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName + "','" + description + "','" + userName + "','" + createOn+ "','" + question_xml + "')";

this.ActionSQLStatement(sSQLStatement);

}

0
dci_burnside
3/14/2007 9:27:35 PM

C# makes no difference.  GetDate() in SQL Server will automatically apply the equivalent of C# datetime.now.  But your database won't complain. Try it.

string sSQLStatement = "INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName + "','" + description + "','" + userName + "',GetDate(),'" + question_xml + "')";

Really, you should be using parameters rather than compiling dynamic SQL statements, but that's another topic. 

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
3/14/2007 9:40:05 PM

nice one, first time i tried it i didn't put ' '  round the GetDate()

Thanks very much for the reply Mikesdotnetting, you really helped me out.

0
dci_burnside
3/14/2007 9:45:39 PM
Reply:

Similar Artilces:

saving .net DateTime to sql database using Stored Procedure...
Hello, I've read many posts on this, but not found any real answers. I have a web method that accepts a DateTime parameter, then writes this value back to a SQL database table.  Sql data type is "datetime" and the column accepts nulls. When the web service is called I get an error, "SqlTypeException: SqlDateTime overflow" or another error of similiar results.When I removed the database call and just write to a label or variable on the page, the web method works fine, but when it saves to the database, the error is thrown. Any ideas on how to handle...

.NET DateTime and SQL Server DateTime problem
.NET DateTime and SQL Server DateTime problem  I have the following if statement in an SQL stored procedure: IF (@Image = 1 AND @DateTimeUploaded = (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))   The value of the @DateTimeUpdated variable was taken from the ImageMainDateTime field when the data was requested by a ASP.NET webpage and is passed back here when that page is submitted so I can compare what the DateTime was when the data was requested with what it is now, in other words I’m doing a concurrency check (I can’t use TimeStamp for many reasons, ...

Compating DateTime field from sql table to DateTime Object
Hi. I have a SQL query that suppose to compare a given DateTime object to a datetime type coloumn : SELECT COUNT(*) FROM [QuestionnairesData] WHERE [SQClientUsername] = '" + Profile.UserName + "' AND [FillingDate] > " + Report.GetLastProductionReportDate(sqClient.SQCLIENTID)  the problem i get is on the last part of the select query. the DateTime object Report.GetLastProductionReportDate(sqClient.SQCLIENTID) gives me : 2/14/2007 15:49:04 PM and when i delete the time part it works well but i need to check for both date and time in this case. there is some issues with DateTime o...

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

How can I update an MS SQL 7.0 datetime field with embedded sql
I have a datetime field in MS Sql 7.0 named Revised_Date. I can update this date with my datawindow but I can't update it with embedded sql. I have tried the following which doesn't work UPDATE dbo.employees SET revised_date = date() ; produces invalid function error UPDATE dbo.employees SET revised_date = 2000-07-31 ; which yeilds 05/17/05 which is incorrect date ld_date time ldt_datetime ld_date = today ldt_date = datetime(ld_date) UPDATE dbo.employees SET revised_date = :ldt_date; which yields error How can I accomplish this ? T...

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

HOW DO I: Insert nulls into SQL dateTime field stored in SQL DB from a web UI textbox
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.    Option 1:     Presently implemented:  Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception   ParamValue    {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime}          ...

save actual time in DateTime field in Sql Server 2005
Hi,        How could I save an actual time instead of 12:00:00 in sql table? I have a column in sql called submission_date of DateTime Datatype. This is how its storing now,  03/03/2008 12:00:00 How could that 12:00:00 be replaced with the actual time the user submits. This is coming from an application in ASP 2.0 C#.Consistency + Intensity = Success   When you save your date and time like 03/03/2008 13:26.01.423 it will save your time with date together into sql serverDon't forget to click "Mark as Answer" on the post(s) that hel...

SQL datetime field
DateTime dt = Convert.ToDateTime(dtlabel.Text); string DT = dt.ToString("MM/dd/yyyy hh:mm:ss"); When I insert  string in database everything work fine but if string is empty I have this 1.1.1900 0:00:00 can I change this to Null or blank tnx.   instead of declaring the string like this string DT = dt.ToString("MM/dd/yyyy hh:mm:ss"); declare the string string DT = string.Empty; And befor assigning the value to string check for null value. if(dtlabel.Text!="") {DateTime dt = Convert.ToDateTime(dtlabel.Text); &nbs...

Dealing with datetime and SQL Transact-SQL
I am trying to make a stored procedure in SQLServer Express.The question is related to this stored procedure / transact - sql.  I think i am doing something wrong with datetime.Here is the stored procedure.The error i am getting is that:Msg 241, Level 16, State 1, Line 20Syntax error converting datetime from character string.  ===================================== DECLARE    @websiteID  intDECLARE    @dateFrom  datetimeDECLARE    @dateTo  datetimeDECLARE    @sortbystring  varchar (20)set @websiteID...

Converting Sql DateTime to C# DateTime
Is there a quick and easy way to convert a DateTime value retrieved from a SQL 2005 query to a C# DateTime datatype?I can change my query to use sql's datepart function to return separate YYYY, MM and DD values and then use the right constructor to create a C# DateTime datatype but I'm just wondering if there's some C# method that will do the conversion for me. Thanks.  If the column in SQL is declared as a datetime then it is real easy to convert it to a .NET datetime object.e.g.lets assume you have a datarow which is a record retrieved from the database and the datetime ...

How to save a picture in a SQL image field and how to show a picture from a SQL image field
HI! I am searching now a real long time in the forum and I found a lot of stuff to this topic. But please help me it doesn't work! On my webapplication there is a possibility to save a private picture.If a new user is saved in the database I want to save a fixed picture as startpicture.Dim picturePath As String = Request.ServerVariables("APPL_PHYSICAL_PATH") & "img\anonymous.jpg" Please tell me the way how to save this anonymous.jpg from the picturePath into the database and after that how to display the picture. Thank you! .         ...

c# datetime type into sql datetime type
hi, i'm trying something that should be very simple...trying to insert a datetime type from a web form into sql datetime columnthe code:commandSql.Parameters.AddWithValue("@dteDOB", this.DOB); //DOB is datetime typethe sql column is dteDOB (sql datetime type)the error i get: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.do i need to convert it to a string when using parameters with value ? what am i missing here ?thanks,Sharon. The problem is usually that the .NET DateTime object does not have the same range as the SQL one. So if you h...

Update datetime on sql database
 I am trying to update a datetime table in my sql database, Can anyone help me format it correct. I am getting error incorrect syntex near 12 and it is 12:51 so the time is not formated correctly for sql to accept My code using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using web = System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Net; using System.Text; using System.IO; using System.Text.RegularExpres...

Web resources about - DateTime unable to save in datetime field of SQL database - asp.net.sql-datasource

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: <span class="utctime">2010-01-01 11:30 PM</span> I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/24/2015 2:23:28 AM