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

 

[PHV]

Use non ambiguous date format, like yyyy-mm-dd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobynegus]

Tried that(Format(datDate, "d/mmm/yyyy")), but got 30/12/1899!!!!!!!!!!!

 

[PHV]

I am runnning an INSERT SQL with IN into another database
What is the SQL code and what is the name of the date field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Use non ambiguous date format, like yyyy-mm-dd

Tried that(Format(datDate, "d/mmm/yyyy")), but got 30/12/1899!!!!!!!!!!!

HUH????

Your format is STILL ambiguous!

You STILL get incorrect results!



Duh!

Skip,

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

 

[tobynegus]

Hi Thanks for the response
I must be a little dumb, can you explain with more detail?
I have tried the following formating
datDate = Format(datDate, "yyyy-mm-dd")
as Skip said, but it still comes out as 11/01/2009 (11th Jan 09)

 

[SkipVought]

Check VB HELP

Format does NOT return a Date Value. Rather, it returns a STRING.

Date Values are NUMERIC not text.

How are you INITIALLY assigning the date value to datDate?

Skip,

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

 

[PHV]

Why not reply to my post timestamped 15 Jan 10 5:09 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobynegus]

sorry about my dimness, i see what you are saying.

the data is collected via a date field from a table, eg 19/11/2009 I am trying to get this to change to first daay of month, eg 01/11/2009, in doing sso I messup up and passed text!!

I will look for a function to change the date to the First Day of Month

Thanks for your patience

Toby

 

[SkipVought]

You are still ignoring PHV's request of 15 Jan 10 5:09.

Skip,

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

 

[tobynegus]

Hi PHV, sorry for the delay I have been away.
Thank you for your help
I am still getting an issue with the date turning up at the other database in american format month date year rather than english format date month year.
The datDate used in the sql is got from a date field of a table. it shows correct in the debug (01/11/2009) before the data is sent accross to the other database but when it gets there is shows up as 11/01/2009.
The INSERT qry is...
INSERT INTO [tbl_AvgTDLF=TFL] ( [Fee Two], [Free Two Description], [TestCodeFee Two], TDLWSLavg, TDLFavg, DataDate ) IN 'F:\mick nov\from c\PathlogyAgregate.mdb' SELECT CostTDLWSL_AVG.[Free Two], CostTFL_Avg.[Free Two Description], CostTFL_Avg.[Free Two], CostTDLWSL_AVG.TDLWSLavg, CostTFL_Avg.TDLFavg, 01/11/2009 AS dat FROM CostTDLWSL_AVG INNER JOIN CostTFL_Avg ON CostTDLWSL_AVG.[Free Two] = CostTFL_Avg.[Free Two];

 

[PHV]

Replace this:
, 01/11/2009 AS dat
with this:
, #2009-11-01# AS dat

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobynegus]

Thanks, can you help a little more...
There must be a simple way to do this but I can't find it.

The date is being passed and inserted into the sql statement, how can I format it to #2009-11-01# without changing it into a string?
Any help much appreciated

Toby

 

[PHV]

The date is being passed and inserted into the sql statement
Post your actual code doing that.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobynegus]

This gets the date from a table record
''''''''''''''''''''''''''
Function GetMonthTDL()
Dim db As Database
Dim rstPathTDL As DAO.Recordset
Set db = CurrentDb
Dim PathTDLDate As Date

On Error GoTo err_norec
Set rstPathTDL = db.OpenRecordset("Pathdata_TDL", dbOpenSnapshot) 'query with records to be aded
rstPathTDL.MoveFirst
PathTDLDate = rstPathTDL("Date_Tdl")
rstPathTDL.Close
Set db = Nothing
GetMonthTDL = PathTDLDate
Exit Function
err_norec:
Exit Function
End Function
''''''''''''''''''''''''''''''''''
I then get the first of the month.
''''''''''''''''''''''''''''''''''
datDate = GetMonthTDL
datDate = DateSerial(Year(datDate), Month(datDate), 1)
'''''''''''''''''''''''''''''''''''''''''''''''''
and put it into the sql
'''''''''''''''''''''''''''''''''''''''''''''''''''
sql = " INSERT INTO tbl_TurnAround ( TestCode, CodeDesc, CntCode, SellPrice, who, [where], ReqEntRsltAvgTA, ReqEntRsltMaxTA, ReqEntRsltMinTA, RsltEntAuthAvgTA, RsltEntAuthMaxTA, RsltEntAuthTA, ReqEntAuthAvgTA, ReqEntAuthMaxTA, RsltEntAuthMinTA, DataDate, DataSource )" '& GetPath & "\PathlogyAgregate.mdb"
sql = sql & dbPath
sql = sql & " SELECT qryBMI_Turnaround.[Test Code], qryBMI_Turnaround.Desc, Count(qryBMI_Turnaround.[Test Code]) AS [CountOfTest Code], qryBMI_Turnaround.Sell, qryBMI_Turnaround.Clinician, qryBMI_Turnaround.[Source(Locn)], Format(Avg([ReqEntRslt]/60),'Fixed') AS ReqEntRsltAvgTA, Format(Max([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMaxTA, Format(Min([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMinTA, Format(Avg([RsltEntAuth]/60),'Fixed') AS RsltEntAuthAvgTA, Format(Max([RsltEntAuth]/60),'Fixed') AS RsltEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAuthTA, Format(Avg([ReqEntAuth]/60),'Fixed') AS ReqEntAuthAvgTA, Format(Max([ReqEntAuth]/60),'Fixed') AS ReqEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAuthMinTA, " & datDate & ", 'BMI' AS DataSource"
sql = sql & " FROM qryBMI_Turnaround"
sql = sql & " GROUP BY qryBMI_Turnaround.[Test Code], qryBMI_Turnaround.Desc, qryBMI_Turnaround.Sell, qryBMI_Turnaround.Clinician, qryBMI_Turnaround.[Source(Locn)], " & datDate & " , 'BMI'"
sql = sql & " HAVING (((qryBMI_Turnaround.Sell)>0));"

 

[PHV]

Replace this (2 times):
, " & datDate & ",
with this:
, #" & Format(datDate, "yyyy-mm-dd") & "#,

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tobynegus]

I have tried putting '#' at each end.
i.e
...[WSL COST], PricesEHT.TestedAt, #" & datDate & "# AS datdat"

this returns 11/01/2009
if i take the # out I get 13/12/1899!!

 

[tobynegus]

I have put it in the SQL
EHT_AutolabWork.[WSL COST], PricesEHT.TestedAt, #" & datDate & "# AS datdat"

But get 11/01/2009
if i put it in the GetMonthTDL function it errors as it is a string?

is this problem to do with American and UK dates?

 

[PHV]

Please, reread my post timestamped 21 Jan 10 5:36

 

[tobynegus]

sorry!
Thank you fopr your patience.

I have put it into the sql, I now get a Syntax error in INSERT INTO statement, (sql below) I do not see where else it should be put,
'''''
...PricesEHT.TestedAt, #" & Format(datDate, "yyyy-mm-dd") & "#, AS datdat"
'''''''''''
the result sql is
''''''''''
INSERT INTO tbl_EHT_AutoLab ( CodeDesc, Code, workload, WSLCost, TestedAt, DataDate ) IN 'F:\mick nov\from c\PathlogyAgregate.mdb' SELECT EHT_AutolabWork.Desc, EHT_AutolabWork.

http://WWWCODE,

EHT_AutolabWork.workload, EHT_AutolabWork.[WSL COST], PricesEHT.TestedAt, #2009-11-01#, AS datdat FROM EHT_AutolabWork INNER JOIN PricesEHT ON EHT_AutolabWork.

http://WWWCODE

= PricesEHT.

http://WWWCODE;

'''''''''''
get error with sql statement