Updating Datetime field 1

[Simboti1]

I am having problems with updating a datetime type field using the INSERT SATEMENT IN Ms Sql 2005. This is the scenario: -
1. dTrxDate is defined as datetime field in Ms Sql 2005.
2. There is no problem if I update the field through: -
2.1 Reading a Record set from the Table.
2.2 .AddNew
2.3 !dTrxDate = DTPicker Text Box on a form.
2.4 .Update

However, when I use the INSERT Statement The field does not gate the Date value from DTPicker Text Box. Instead, a null is moved to the field.

Please advise

 

[Andrzejek]

Could you show your INSERT statement along with [tt]Debug.Print[/tt] if you do something like:

strSQL = "INSERT INTO TableName (Field1, ...) VALUES (..."

Debug.Print strSQL

Or any other code you have for your INSERT.

Have fun.

---- Andy

 

[Simboti1]

Hi Andy

Below is my sample code
------------------------------------------------------------
strQuery = " INSERT INTO CLAIMSHDR " _
& "(BATCH_ID, BATCH_DATE, BATCH_DESCRIPTION, RECORD_COUNT, USER_ID) VALUES (" _
& "'" & strBATCH_NUMBER & "'" & "," & "'" & dtpBATCH_DATE & "'" & "," _
& "'" & strBATCH_DESCRIPTION & "'" & "," & "'" & intRECORD_COUNT & "'" & "," _
& "'" & strUSER_ID & "'" & ")"
------------------------------------------------------------
INSERT INTO CLAIMSHDR (BATCH_ID, BATCH_DATE, BATCH_DESCRIPTION, RECORD_COUNT, USER_ID)
VALUES ('09070009','18/04/2009','Medsys Claims for Batch Number: 09070009','0','ADMIN')
------------------------------------------------------------
1. Running the above code will update the BATCH_DATE field with a Null.
2. If I take the '' from 18/04/2009, the BATCH_DATE field is updated with 01/01/1900 12:00:00 AM.
My interpretation is that 18/04/2009 is being converted to a zero before the update.
3. If I use '#18/04/2009#', the error is "Conversion failed when converting datetime from character string."
4. If I use #18/04/2009#, the error is "The name "#18" is not permitted in this context"

 

[SkipVought]

ALWAYS use the UNAMBIGUOUS

yyyy/mm/dd

format!!!

#2009/04/18#

otherwise, gates & co, ASSUMES m/d/y in most cases

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

After a few clicks on Google, it looks to me you need something like:

Convert(DateTime, '2009/18/04')[green]
'or[/green]
Cast('2009/18/04' as DateTime)

Have fun.

---- Andy