SqlCommand INSERT into smalldatetime column

[jtaylor]

I'm trying to insert a row into a SQL Server table but I keep getting
a "Conversion failed when converting character string to smalldatetime
data type." error.

This code works:
sqlCmd = New SqlCommand("INSERT INTO tableName" _
+ " (colDate, colCount)" _
+ " VALUES ('1/9/2010
12:00:00 AM', 1)" _
, connSQL)
sqlCmd.ExecuteNonQuery()

While this does not:
sqlCmd = New SqlCommand("INSERT INTO tableName" _
+ " (colDate, colCount)" _
+ " VALUES ('@Date', 1)" _
, connSQL)
sqlCmd.Parameters.Clear()
sqlCmd.Parameters.AddWithValue("@Date", "1/9/2010 12:00:00
AM")
sqlCmd.ExecuteNonQuery()

What am I overlooking?

VB.NET & VS2005

[vinay kumar]

rather than mentioning date u can use getdate() function sqlserver

[jtaylor]

Won't the getdate() function return a full timestamp? I need the time
portion to always be midnight.

On Jan 12, 2:35 am, vinay kumar <namavinayku...@gmail.com> wrote:
> rather than mentioning date u can use getdate() function sqlserver
>

[Jamie Fraser]

This line

sqlCmd.Parameters.AddWithValue("@Date", "1/9/2010 12:00:00
AM")

Adds your parameter as a type string (which gets converted to a varchar by SQL)

Try changing it to

sqlCmd.Parameters.AddWithValue("@Date", New DateTime(1,9,2010))

Or something similar.

[vinay kumar]

and also u can use convert function of sqlserver

 it consists of 3 paramaters 

[jtaylor]

I tried this

sqlCmd.Parameters.AddWithValue("@Date", New DateTime(1,9,2010))

but it gave me a "Year, Month, and Day parameters describe an un-
representable DateTime". I changed it to be Year, Month, Day, and
then I was back to the conversion error on the .ExecuteNonQuery().

On Jan 13, 5:36 am, Jamie Fraser <jamie.fra...@gmail.com> wrote:
> This line
>
> sqlCmd.Parameters.AddWithValue("@Date", "1/9/2010 12:00:00
> AM")
>
> Adds your parameter as a type string (which gets converted to a varchar by SQL)
>
> Try changing it to
>
> sqlCmd.Parameters.AddWithValue("@Date", New DateTime(1,9,2010))
>
> Or something similar.
>

[Anachronistic]

jtaylor,

Try using the .ToShortDateString() method provided via the DateTime
object. For example, if you want to add by value, use
DateTime.Now.ToShortDateString() and see if the current time is
inserted as you expected. If so and you need to insert other date
values, you'll need to establish new DateTime objects and set
everything accordingly before inserting the .ToShortDateString()
value.

I am not currently at a machine to test this, so please let us know if
you find it successful (or if you don't!)

Alan

http://www.twitter.com/anachronistic

[Arsalan Tamiz]

sqlCmd = New SqlCommand("INSERT INTO tableName" _
                                            + " (colDate, colCount)" _
                                            + " VALUES ('@Date', 1)" _
                                    , connSQL)

and this line you put @Date in single quotes is this right?

[Hozefa Unwala]

Use convert.todatetime()

--
Hozefa Unwala

[jtaylor]

I'm afraid it still gave the conversion error.

[akram mellice]

the line sqlCmd = New SqlCommand("INSERT INTO tableName" _

                                           + " (colDate, colCount)" _
                                           + " VALUES ('@Date', 1)" _
                                   , connSQL)

replace with sqlCmd = New SqlCommand("INSERT INTO tableName" _
                                           + " (colDate, colCount)" _
                                           + " VALUES (@Date, 1)" _
                                   , connSQL)

--
Akram Mellice

[Jamie Fraser]

sqlCmd = New SqlCommand("INSERT INTO tableName (colDate,
colCount) VALUES (@Date)", connSQL)
sqlCmd.Parameters.Clear()
sqlCmd.Parameters.AddWithValue("@Date", New DateTime(2010, 1, 19))
sqlCmd.ExecuteNonQuery()

[jtaylor]

The single quotes in SqlCommand were the problem. I dropped them and
it worked fine.

Thanks everyone for your help!