nedd some help with datetime

Hi,
I am passing datetime from my vb.net app for storage in sql server like this
Dim DT As DateTime = DateTime.Now
Dim str As String
str = DT.ToString("MMddyyyyHHmmss")
...insert to database str (example datetime 10312005135802)
The table column datatype is char(15). How can I convert this to datetime in
sql server? I have an sp that need to look at both the date and time. I was
trying this
declare @.dateandtime datetime
select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea where
id = 1234
select @.dateandtime
I am getting the error "syntax error converting datetime from character
string".
Or should I change the format I am sending the datetime from my vb.net app?
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01A96FD0-238C-42A8-AEF5-3BE330EC9EDC@.microsoft.com...
> Hi,
> I am passing datetime from my vb.net app for storage in sql server like
> this
> Dim DT As DateTime = DateTime.Now
> Dim str As String
> str = DT.ToString("MMddyyyyHHmmss")
> ...insert to database str (example datetime 10312005135802)
> The table column datatype is char(15). How can I convert this to datetime
> in
> sql server? I have an sp that need to look at both the date and time. I
> was
> trying this
> declare @.dateandtime datetime
> select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea
> where
> id = 1234
> select @.dateandtime
> I am getting the error "syntax error converting datetime from character
> string".
> Or should I change the format I am sending the datetime from my vb.net
> app?
> Thanks
Any reason why you appear to be using dynamic SQL to do this? Pass the date
as an adDBTimeStamp type using the ADO parameters collection. That way the
conversion to SQL DATETIME is implicit. Also, you should typically call a
proc for a simple INSERT rather than constuct a dynamic SQL string in code.
See the Using Parameters topic in the ADO section of Books Online.
David Portas
SQL Server MVP
--|||> The table column datatype is char(15).
WHY? Do you put ice cream in your medicine cabinet too?
Pass it as a proper date time value, store it as a proper date time value,
and you will be amazed how many of these silly string conversion problems
magically disappear.|||Honestly, I was thinking the same thing, however, I have noticed in a few
articles a while ago where data is stored as strings and taught I was wrong
all along to use datetime type in my database when I need to pass and store
date and time form my front end app.
Thanks for rebooting that section.
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHY? Do you put ice cream in your medicine cabinet too?
> Pass it as a proper date time value, store it as a proper date time value,
> and you will be amazed how many of these silly string conversion problems
> magically disappear.
>
>