SQL datetime overflow. Vb.net

Question

I have a problem saving the system date.

In my table I have a column date (datetime)

But I get a "SQL datetime overflow" error in my project.

This is how I input my data

 Dim today As System.DateTime

 Dim cmdAddTransaction As New SqlCommand("INSERT INTO [Scanner].[dbo].[Transaction]([ID], [Name], [Description], [Date]) VALUES(@ID, @Name, @Description, @Date)", Connection)
 cmdAddTransaction.CommandType = CommandType.Text

 cmdAddTransaction.Parameters.AddWithValue("@ID", frm_main.txtbox_id.Text)
 cmdAddTransaction.Parameters.AddWithValue("@Name", txtbox_Fname.Text)
 cmdAddTransaction.Parameters.AddWithValue("@Description", "Edit Account")
 cmdAddTransaction.Parameters.AddWithValue("@Date", today)

 cmdAddTransaction.ExecuteNonQuery()

 MsgBox("User Successfully Updated")

But as I said I get an error. Can please someone help me I want the date to be formatted like mmm/dd/yyyy if its possible.

why you are using varchar(max) DataType for Date column,use DateTime Datatype — HaveNoDisplayName, Aug 16 '15 at 06:42
Will that solve it? and is it in a mmm/dd/yyyy format? @HaveNoDisplayName — Kristale Genessa Ordiz-Magallo, Aug 16 '15 at 06:47
Where do you initialize the `today` variable? I don't see it in your code... — Zohar Peled, Aug 16 '15 at 08:53
@ZoharPeled it's on the very top code sir. `Dim today as system.datetime` — Kristale Genessa Ordiz-Magallo, Aug 16 '15 at 08:55

score 1 · Accepted Answer · answered Aug 16 '15 at 08:58

1

Sql server's Datetime data type valid range of dates is between January 1st 1753 and December 31th 9999.

In your code, you don't initialize the today variable. That means that it's value is January 1st 0001. Clearly way before January 1st 1753.

Based on the name of the variable, I imagine it should be today's date, so you should write something like Dim today as System.DateTime = DateTime.Today when you declare the variable.

answered Aug 16 '15 at 08:58

Zohar Peled

79,642
10
69
121

I declared it but when I included the `=datetime.today` it works fine now. – Kristale Genessa Ordiz-Magallo Aug 16 '15 at 09:02
One last question. How can I make it a mmm/dd/yyyy format without the time? – Kristale Genessa Ordiz-Magallo Aug 16 '15 at 09:04
1

@KristaleGenessaOrdiz-Magallo: define the column as datatype `DATE` - that's date only, no time portion. Also: **don't call your column** `date` - it's a T-SQL reserved keyword - use a **more meaningful** name, like `OrderDate`, `TransactionDate`, `VisitDate` or something - not just `date` .... – marc_s Aug 16 '15 at 09:06
2

I agree with @marc_s completely. Also, you should know that [date values are not stored with display format in sql](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) (nor in .net, btw). you can use `Convert` or `Format` (depending on your sql server version) on the select statement, or simply format the values in the presentation layer (better practice.) – Zohar Peled Aug 16 '15 at 09:10

SQL datetime overflow. Vb.net

1 Answers1