• 0

[SQL] Changing all dates to 14/mm/yyyy


Question

Howdy,

I have a whole bunch of dates in one of my tables in MS Access. They're currently whatever they are, but I'm wanting to change them to all to 14/mm/yyyy (for billing purposes). How might I go abouts this in SQL! Hmm, any hints?

Cheers.

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Give us a quick breakdown of the table format ???

Maybe one record that you have extracted?

This way we could format a SQL statement that would work for you.

Link to comment
Share on other sites

  • 0

Okies... I have a table called Account. It currently has an attribute called AccountDate. I'd like to change the value for every row of AccountDate to 01/mm/yyyy (noting that I need to keep the mm/yyyy the same as what it currently is) :D Cheers for help.

I have a bigger problem... but that can wait :)

Link to comment
Share on other sites

  • 0

Maybe try:

UPDATE Account SET AccountDate = CONCAT('01',RIGHT(AccountDate,8))

Not sure Access SQL allows CONCAT()...

Or maybe:

UPDATE Account SET AccountDate = FORMAT(AccountDate,'01\/mm\/yyyy')

Something close to that should work I guess.

Link to comment
Share on other sites

  • 0

Heya, yeah, don't think CONCAT is supported.

Q) How does FORMAT(AccountDate,'01\/mm\/yyyy') work? (How are you using \/ here?) Looking up the Format() function in Access help gives me:

Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

Q) If it's returning a String, will that cause me problems since it's updating attribute that is of Date datatype? Or does it have something to do with the fact that it's a Variant so it doesn't matter?

How about this super long winded way that works :) But it's kinda crazy... changes it all to 14/mm/yyyy.

DateSerial(Year([AccountDate]), Month([AccountDate]), (Day([AccountDate])-Day([AccountDate])+14))

Cheers for help :D

Link to comment
Share on other sites

  • 0
Q) How does FORMAT(AccountDate,'01\/mm\/yyyy') work? (How are you using \/ here?)

'01\/mm\/yyyy' is a format, \/ is a / that needs to be escaped with a \.

Using a string is ok. A Date is a string format correctly (in SQL syntax).

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.