CODEWITHSUNDEEP
sql-servert-sqldatedate-formatting
Samuel
Samuel

Reputation: 39

Changing date format in SQL Select Statement

Hi I have written a query to extract the create date and I would like the output to be YYYYMM. However, the existing format in the database returns YYMM. How do I do the conversion? I have tried to run the code below and it returns an error saying that I have a missing expression.

SELECT CONVERT(VARCHAR(6,[CREATED_DATE], 120) as 'CREATED_DATE' 
FROM SGTEL10.B$GC_NETELEM
WHERE IPID IN (4001702, 4006023, 4320791);

Upvotes: 0

Views: 3085

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272006

SQL Server: use the FORMAT function to format dates:

SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyyMM')
-- 201904

PL/SQL: use TO_CHAR function:

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMM') FROM DUAL
-- 201904

Upvotes: 1

Rima
Rima

Reputation: 1455

SELECT convert(nvarchar(10), datepart(year,getdate ())) + ' ' + convert(nvarchar(10), datepart(month,getdate ()))

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Try using format mask 112, with a varchar(6) (i.e. 6 characters wide):

SELECT CONVERT(VARCHAR(6), GETDATE(), 112);

This outputs:

201904

Your full updated query:

SELECT CONVERT(VARCHAR(6),[CREATED_DATE], 112) AS [CREATED_DATE]
FROM SGTEL10.B$GC_NETELEM
WHERE IPID IN (4001702, 4006023, 4320791);

Tech on the Net is my favorite reference for looking up the SQL Server CONVERT format masks.

Upvotes: 1

Related Questions