Converting Date Type

[CP611]

Hello,

I am looking to run a query that will change a date from one format into another. The current format is Jan 9, 2024. Whereas I would want it to show 09/01/2024. I have used the below query in the past the transfer it from another format into the one I am after so this is the sort of thing I would need again

Format(CDate(Replace(Left([Submission Date],InStr([Submission Date]," ")-1),":","/")),"dd/mm/yyyy")

Thank you

[ranman256]

=DateAdd("d",-1,[[submission date])

[Bob Fitz]

=DateAdd("d",-1,[[submission date])

Should that be:
DateAdd("d",0,[submission date])

[Welshgasman]

tt=#Jan, 9 2024#
? tt
09/01/2024

[CJ_London]

Dates are stored as numbers so all you need to do is set the format property of whatever control is displaying the value, no need for a query

or are you saying your date field is actually a string?

[Micron]

To add to all that, there is no point in wrapping CDate inside of Format because format will change it back to a string, so one might as well just work with a string?

[CP611]

Should that be:
DateAdd("d",0,[submission date])

this worked, thank you!

[CP611]

I have another one where the date is showing as 09/01/2024 13:02:00 but I want it to just show as 09/01/2024. Even if I change it to short date on the excel spreadsheet before putting it in it still keeping the time in as 00:00:00 which I want to get rid of. What could the query be to change it to the correct format?

[Welshgasman]

Use Datevalue()

A format is just that a *format*. The cell will still have a time element in it.

[Bob Fitz]

I have another one where the date is showing as 09/01/2024 13:02:00 but I want it to just show as 09/01/2024. Even if I change it to short date on the excel spreadsheet before putting it in it still keeping the time in as 00:00:00 which I want to get rid of. What could the query be to change it to the correct format?

Perhaps:
DateAdd("d",0,Left([submission date],8))

[CP611]

Hi there, unfortuantely that one didn't work it just converted it into a blank field

[ArviLaanemets]

To remove time part try Int([submission date])
(Must work when your 'date' is really a Date!)

[Welshgasman]

Hi there, unfortuantely that one didn't work it just converted it into a blank field

Who are you replying to?

[CP611]

hi there, thank you for the advice so far! sadly none of the options have worked so far, it still turns it into a blank field

[Welshgasman]

So it is not a date type?
Use CDate() in that case.