Insert date into SQL Server 2005 Table

If that is the format (mm-dd-yy), wrap the 'string' in a convert statement.

e.g.
INSERT INTO MyTable (MyDateColumn)
VALUES (CONVERT(datetime,'12-04-07 13:22:11',1))

Hi Guys

The date is a variable that will change.
I cannot code the actual date into the SQL command, I can only code the variable.

The variable name is: OrderPOSDate
The Variable is originally formatted like so: 12-04-07 13:22:11
The database column datatype is datetime


dbaduck:
INSERT INTO dbo.table (column1) VALUES ( '12-04-2007 13:22:11' )
I understand that, and I thought that would work, but it doesn't and I am currently using this:

INSERT INTO dbo.table (column1) VALUES ( '"&OrderPOSDate&"')
the inserted value ends up being:
7/2/2007 12:00:00

If I change it to:
INSERT INTO dbo.table (column1) VALUES ( "&OrderPOSDate&")
which is the 'date' you are suggesting, the inserted value ends up being: :
12/30/1899 12:00:00 AM

???

Nightman..

with:
CONVERT(datetime,OrderPOSDate,1)

I get type mismatch

It should be

INSERT INTO dbo.table (column1) VALUES ( '"&OrderPOSDate&"')"

and for clarity it should be

( apostrophe quote &OrderPOSDate& quote apostrophe ) quote
' " &OrderPOSDate& " ' ) " without the spaces.  Make sense?  What looks like is happening is that your string is not formatted as you may expect and you also may look at your Locale settings (Regional Settings) in Windows on that SQL Server to make sure that you have different regional settings than US.

The other thing that you should look at is doing a
SELECT CONVERT(datetime, '" &OrderPOSDate&"')
and seeing what you get back.

EGormly said:
>>The Variable is originally formatted like so: 12-04-07 13:22:11

Just a little advice: try to use formats that do *not* depend on regional settings.  I would urge
you to use something like yyyy-mm-dd hh:mm:ss

Give this a try:

CONVERT(datetime,REPLACE('12-04-07 13:22:11','-','/'),1)

dbaduck:

That is what I was doing originally and I was getting the 7/2/2007 12:00:00 value
I just did a copy/paste just to make sure and again, with your code I get: 7/2/2007 12:00:00

My regional settings are correct.
The convert statement you gave me gives me errors..
I pasted it into a SQL Query window and I get this:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

But I really have no clue as to what I am doing there.
I may have the code wrong...

I am doing a SELECT CONVERT(datetime, '" &OrderPOSDate&"') from [dbo].[orders] directly from the query window in SQL Studio Management Studio

SELECT CONVERT(datetime,REPLACE(OrderPOSDate,'-','/'),1)

What does that return?

Nightman:
CONVERT(datetime,REPLACE(OrderPOSDate,'-','/'),1)

gave me the same result. 7/2/2007 12:00:00

Oh a whim I just changed the original variable in the file I am parsing to see what would happen.
I charged the original value from 12-04-07 13:22:11 to 12-03-07 13:22:11

and still got 7/2/2007 12:00:00

What are your regional settings?

Then change the original value to 2007-12-03 13:22:11 and see what you get.