converting datetims variable string to datetime sql server

Question

Post reply

converting datetims variable string to datetime sql server

mazal0404

Old Hand

Points: 343
More actions
September 29, 2008 at 6:39 am

#71923

I try to insert into table a string variable date to column datetime:
my Query:
"INSERT INTO tblDate (myDate , WorkerNum) VALUES ( ' + d + ',
'" + workerNum + "')";
but its give me this error :
"Conversion failed when converting datetime from character string"
I know that the format in column datetime sql is ad/mm/yyyy 00:00:00
and the format in my variable "myDate" in c# is dd/mm/yyyy, but i can't convert any of them. What can i do?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mazal0404 Old Hand Points: 343 More actions · Answer 1

I forgot to add that I try to convert "myDate" like this:

DateTime d = DateTime.Parse(myDate);

but without success.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

check out the CONVERT function in SQL Server, it will let you explicity decide what format the string is in to convert to date.

CONVERT(datetime, @mystringdatevariable, 103)

for examle, would expect the string to be in 'DD/MM/YYYY' format.

mazal0404 Old Hand Points: 343 More actions · Answer 3

Its been used like this:

"INSERT INTO tblDate (CONVERT(varchar,myDate ,103), WorkerNum) VALUES ( ' + date + ', '" + workerNum + "')";

this time i get error: 'syntax incorrect near the conver' somthing like this

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

No... you have the conversion on one of the columns in the Insert list... it has to be on the Date column in the Values list, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

mazal0404 (9/29/2008)
"INSERT INTO tblDate (myDate , WorkerNum) VALUES ( ' + d + ',
'" + workerNum + "')";

According to this, you're trying to convert the string ' + d + ' into a datetime value. Those characters do not form any kind of datetime value no matter what format you specify.

If you are generating a string to execute as dynamic sql, then the result should look like this:

INSERT INTO tblDate(myDate, WorkerNum) VALUES ('01/01/2008', 42)but the actual string being generated from your example above will look like this:

INSERT INTO tblDate(myDate, WorkerNum) VALUES (01/01/2008, '42')Having the quotes around 42 (or whatever value WorkerNum contains) is unnecessary but not a problem -- it will convert 42 to a string then implicitly convert it back to a number. (I'm assuming the WorkerNum is a numeric type.) However, the date string MUST have quotes around it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Ronzo Hall of Fame Points: 3917 More actions · Answer 6

I think what you need is some more quotes:

NOT

"INSERT INTO tblDate (myDate , WorkerNum) VALUES ( ' + d + ',

'" + workerNum + "')"

BUT

"INSERT INTO tblDate (myDate , WorkerNum) VALUES ( '" + d + "',

'" + workerNum + "')"

Have Fun!
Ronzo