SQL date formats- 01/11/09 not 11/01/09 1

[tobynegus]

I am runnning an INSERT SQL with IN into another database

I am having trouble with date format

The date the sql is getting is 01/11/09 (1st Nov 09)
(this shows correct in the Immediate panel)

when the data gets to the other database in comes in as
11/01/09 (11th Jan 09)

how can i get this correct?
any help much appreciated
Toby

 

[tobynegus]

DONE IT!!!!!!!!!!!

thanks

I altered the Insert into to get rid of the error and I now get the correct date at the other end.

Thank you for your patinece

I do not understand really what is going on, why it changes, from 01/11/2009 to 11/01/2009, but tthankk you

Toby

 

[SkipVought]

[tt]
...PricesEHT.TestedAt, #" & Format(datDate, "yyyy-mm-dd") & "# , AS datdat"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, toby, here at Tek-Tips, its not good enought to merely state, "I fixed it. Thanks!"

Other members browsing these threads want to know specifically WHAT solution you arrive at.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tobynegus]

thanks, I will provide a better response next time.

Thank you again for your help.

Any news on WHY this was happening?

Toby

 

[SkipVought]

Regional settings vs default American format (Gates & Co).

Bottom Line: As PHV pointed out, _always, always, ALWAYS, ALWAYS use the UNAMBIGUOUS string format, yyyy-mm-dd.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tobynegus]

thanks