Access equivalent of convert() function

[Gill1978]

Hi,

Does anyone one know the what the access equivalent of the sql server command

CONVERT(DATETIME, [date], 103) <--- that is, british australian time.

is in Microsoft Access 2000?

The above statement is not recognising the convert statement in my query ...

Thanks

Julie

 

[PHV]

Have a look at the Format function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gill1978]

Tried the format function as in :

FORMAT([date], "Small Date") ...

and I got the following error:
dts_CIN_SMT_TRADES4: [Microsoft Data Transformation Services (DTS) Data Pump(-2147213206): The number of failing rows exceeds the maximum specified.]

Have I got the function wrong?

 

[lameid]

Use Cdate to convert any data to a date (assuming it can).

Then Use format to display it however your want. *Format returns a string.

I'm guessing what you want is...

FORMAT(CDATE([date]), "dd/mm/yyyy")

Unless I have it backwards and you want

FORMAT(CDATE([date]), "yyyy/mm/dd")

If your data is already a date, you don't need CDATE.

There are several named format types to but I don't know them off the top of my head and they will change with the computer's regional settings which may be desirable or not.

 

[Zion7]

All suggestions above, are more flexible, but just for the record,

Gill, you were very close with FORMAT([date], "Small Date")
but in Access, it is FORMAT([date], "Short Date")

 

[Gill1978]

Hi All,

Thanks for all the ideas ... but non of them worked but I have found a solution :

DateSerial(left(TestDate, 4), mid(TestDate, 5, 2), right(TestDate, 2))

Thanks again ...

Julie Gill

 

[PHV]

Bottom line: TestDate isn't a date field and thus no date function may work.