fault Posted October 2, 2004 Share Posted October 2, 2004 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 More sharing options...
0 RazorSA Posted October 2, 2004 Share Posted October 2, 2004 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 More sharing options...
0 fault Posted October 2, 2004 Author Share Posted October 2, 2004 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 More sharing options...
0 Mouton Posted October 2, 2004 Share Posted October 2, 2004 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 More sharing options...
0 fault Posted October 2, 2004 Author Share Posted October 2, 2004 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 More sharing options...
0 Mouton Posted October 2, 2004 Share Posted October 2, 2004 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 More sharing options...
Question
fault
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