SQLTeam.com | Weblogs | Forums

Update a date column to another date format


#1

I am trying to update a table with an already formatted date column of mm/dd/yy to a medium date format...example dd-Mmm-yy..I'm getting the following error when execute: Msg 195, Level 15, State 10, Line 4 'FORMAT' is not a recognized built-in function name.

Here is the script:
UPDATE RTL
SET D_ANALYZED = FORMAT([D_ANALYZED],'Medium Date'), D_COLLECTED = FORMAT(D_COLLECTED,'Medium Date'), T_COLLECTED = LEFT(T_COLLECTED,2)+RIGHT(T_COLLECTED,2), D_END = FORMAT(D_END,'Medium Date'), D_START=FORMAT(D_START,'Medium Date')
FROM RTL

What other function to use?


#2

Strongly recommend that you don't store a formatted date in the table, but rather store it as a DATE (or DATETIME) datatype - and then Format the date when it is retrieved (or, better still, format it in the Application - so that the application, itself, receives the date as a Date datatype native object and can thus manipulate it as a date, rather than as a String)

All sorts of grief and side effects come about from trying to parse dates which are in Text format, whereas when starting with a date in a DATE / DATETIME datatype then using Date functions, sorting by date, finding all dates with a Start/End period etc. all work perfectly and, more importantly, without any unexpected side effects and edge-condditions!


#3

The data comes from one place formatted one way and then the data is massaged to load to another place. It is during the massage time that I need to reformat the dates in those columns not the datatype. The table itself is set as a datatype of DATE for the columns specified before the massage. I don't need to change the datatype of the column just the format before I can transfer the data on to the other place. The table datatypes need to stay the same as when the data was transferred the first time for the next transfer.


#4

SQL Server doesn't have "medium date" as a format (that's more like an Access format (maybe?)).

If the input format is strictly mm/dd/yy, ('01/01/16' rather than 1/1/16') you can do this to reformat as 'YYYYMMDD' which is accurate under all SQL settings and inherently sortable as well, btw.

UPDATE RTL 
SET D_ANALYZED = '20' + SUBSTRING([D_ANALYZED], 7, 2) + REPLACE(LEFT([D_ANALYZED], 5), '/', ''),
    D_COLLECTED = '20' + SUBSTRING([D_COLLECTED], 7, 2) + REPLACE(LEFT([D_COLLECTED], 5), '/', ''),
    T_COLLECTED = LEFT(T_COLLECTED,2)+RIGHT(T_COLLECTED,2), 
    D_END = '20' + SUBSTRING([D_END], 7, 2) + REPLACE(LEFT([D_END], 5), '/', ''),
    D_START = '20' + SUBSTRING([D_START], 7, 2) + REPLACE(LEFT([D_START], 5), '/', '')
FROM RTL

#5

Thanks, but what is the format for the following:

from YYYY-MM-DD to DD-Mmm-YY


#6

Nevermind, I got what I needed by using this:

D_ANALYZED = CONVERT(VARCHAR(20),CAST([D_ANALYZED] as datetime), 6)

Thanks to everyone for the info.