Concatenate Two Values On Insert - SQL

Question

I'm attempting to accomplish what seems to be something simple, but I'm unable to find a solution - I'm probably wording it incorrectly.

I have a third party software that outputs date and time as two separate values, one for date and the other for time, and I need to be able to INSERT that data as one datetime field into SQL.

The third party software is built on a proprietary language based on .NET, and using SQL Server 2005.

Here are my two values in the software:

let cbdate1 = cbdate
let cbtime1 = cbhr & ":" & cbmn & ":00 " & ampm

And then the SQL would go as:

DOSQL "INSERT INTO Leads (DateTimeField) VALUES (cbdate1 + ' ' + cbtime1)"

So essentially, I am attempting to join the two values upon insert into the table. That does not work, and I'm unable to direct myself to find an appropriate answer.

Additional Information:

The Date (cbdate1) is presented as "MM/DD/YYYY" and the Time is simply joined as presented "HH:MM:00 AM"

Answer 1

You are currently using double quotes you should instead use single quotes since that is a valid string in SQL.

 DOSQL "INSERT INTO Leads (DateTimeField) VALUES (cbdate1 + ' ' + cbtime1)"

Edit:

Now if you get further problems it might be because your DateTimeField is a datetime datatype. Now you could then after concatenating convert or cast the string to the correct format.

Like:

 DOSQL "INSERT INTO Leads (DateTimeField) VALUES (Convert(datetime, cbdate1 + ' ' + cbtime1))"

Edit #2:

Without a 24 hour part you would need a mon dd yyyy format ex: Oct 22 2012. Otherwise you might have to try and get the time part into a 24 hour format.