CODEWITHSUNDEEP
sqlsql-servert-sql
Carlos Diaz
Carlos Diaz

Reputation: 11

Formatting date issue

I have the following query:

SELECT ACCOUNTNUMBER, PROCESSDATE
FROM INVENTORY

Result:

ACCOUNT   PROCESSDATE
5646546   11082021
4654646   11082021

The date is in the wrong format.

NOTE: I checked the table design and the PROCESSDATE field seems to be an integer.

What code I have tried:

.-format(PROCESSDATE, 'DD/mm/yyyy') as PROCESSDATE [the result is DD/mm/yyyy in the column)
.-CONVERT(date, CONVERT(varchar(6), PROCESSDATE) + '01') myDate [The result is an error]
.-CONVERT(CHAR(10), PROCESSDATEAS Datetime) as 'MyDateTime' [the result is an error]

Desired output: Obtain PROCESSDATE field as MM/dd/yyyy format.

Upvotes: 0

Views: 130

Answers (3)

SteveC
SteveC

Reputation: 6015

You could use DATEFROMPARTS to parse the parts and then format 101 to put in MM/DD/YYYY format. Something like this

SELECT ACCOUNTNUMBER, 
       PROCESSDATE,
       calc.dt,
       convert(varchar(12), calc.dt, 101) vc_with_date_format
FROM (values (5646546, 11082021),
             (5646546, 11082021))
             INVENTORY(ACCOUNTNUMBER, PROCESSDATE)
     cross apply (values (datefromparts(right(PROCESSDATE, 4),
                                        substring(cast(PROCESSDATE as char(8)),3,2),
                                        left(PROCESSDATE, 2)))) calc(dt);

[EDIT] Without the virtual table (which was for demo only)

SELECT i.ACCOUNTNUMBER, 
       i.PROCESSDATE,
       calc.dt,
       convert(varchar(12), calc.dt, 101) vc_with_date_format
FROM INVENTORY i
     cross apply (values (datefromparts(right(i.PROCESSDATE, 4),
                                        substring(cast(i.PROCESSDATE as char(8)),3,2),
                                        left(i.PROCESSDATE, 2)))) calc(dt);

ACCOUNTNUMBER   PROCESSDATE dt          vc_with_date_format
5646546         11082021    2021-08-11  08/11/2021
5646546         11082021    2021-08-11  08/11/2021

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is a horrible format. Note that if the day is less than 10, then the length of the integer changes. Arggh!

So, my recommendation is to convert to an 8-character string (with a leading '0' if necessary), then construct a canonical date string ('YYYYMMDD'). And convert to a date:

select convert(date,
               right(format(processdate, '00000000'), 4) + substring(format(processdate, '00000000'), 3, 2) +left(format(processdate, '00000000'), 2)
              )

You can actually move the format() to a subquery, CTE, or values clause, as in:

select convert(date, right(v.processdate_str, 4) + substring(v.processdate_str, 3, 2) +left(v.processdate_str, 2))
from inventory i cross apply
     (values (format(i.processdate, '00000000'))
     ) v(processdate_str)

Here is a db<>fiddle.

Upvotes: 1

sacse
sacse

Reputation: 3744

I think your dates are stored as ddMMyyyy in the DB. You can get the desired result by applying the following conversion:

CONVERT(VARCHAR, CONVERT(DATETIME,STUFF(STUFF(PROCESSDATE, 5, 0, '/'), 3, 0, '/'),103), 101)

Upvotes: 0

Related Questions