1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Change date format of whole column

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 15, 2021.

  1. Stack

    Stack Membro Participativo

    I have tried to convert format of my date column in various ways available online but nothing seems to help.

    I want to convert given date into DD/MM/YYYY Currently I don't really know in which version they are because I tried all the Standard Date Time Code. For Example Date displayed at 1 is 2015-01-14, I want it to be displayed as 14/01/2015

    Here is what I want to do :: I want other quarters also to be displayed like the format specified in query but I think due to date it is selecting it as Month 1 everywhere instead of Date 1

    DECLARE @Date2 DATETIME= GETDATE();
    SELECT @Date2 AS OriginalDate;
    SELECT DATEPART(Quarter, @Date2) AS Quarter_Month;

    WITH Quarters AS (
    SELECT Q = 'JFM', MonthBegin = 1, MonthEnd = 3 UNION
    SELECT Q = 'AMJ', MonthBegin = 4, MonthEnd = 6 UNION
    SELECT Q = 'JAS', MonthBegin = 7, MonthEnd = 9 UNION
    SELECT Q = 'OND', MonthBegin = 10, MonthEnd = 12
    )
    SELECT
    --[Year] = DATEPART(yyyy, CONVERT(DATETIME, MonthYear)),
    [Quarter] = CONVERT(VARCHAR(4), DATEPART(yyyy, CONVERT(DATETIME, Date2))) + '-' + q.Q
    FROM
    Transactions AS Dates --(MonthYear)
    INNER JOIN Quarters q ON
    DATEPART(m, CONVERT(DATETIME, Date2)) >= q.MonthBegin AND
    DATEPART(m, CONVERT(DATETIME, Date2)) <= q.MonthEnd;


    Date2 Column looks like this. 2015-01-08 00:00:00.0000000 Date2 is added by SQL query alter and then datatype updated using ALTER COLUMN Date2 dATETIME2 then this is used UPDATE Transactions SET Date2 = CONVERT(datetime2,MonthYear,103)

    Vertical Plant OrderType Customer SalesType ProductGroup MonthYear Sales Budget Plan Variance
    5 4 7 1 1 2 1/4/14 0 0 0 0
    5 4 7 1 1 2 1/5/14 0 0 0 0
    5 4 7 1 1 2 1/6/14 0 0 0 0
    5 4 7 1 1 2 1/7/14 0 0 0 0
    5 4 7 1 1 2 1/8/14 0 0 0 0
    5 4 7 1 1 2 1/9/14 0 0 0 0
    5 4 7 1 1 2 1/10/14 0 0 0 0
    5 4 7 1 1 2 1/11/14 0 0 0 0
    5 4 7 1 1 2 1/12/14 0 0 0 0
    1 1 1 1 1 1 1/1/15 295.84 0 0 -295.84
    1 1 1 1 1 2 1/1/15 10.43 0 0 -10.43
    1 1 2 1 1 1 1/1/15 2.1 0 0 -2.1
    1 1 2 1 1 2 1/1/15 0.88 0 0 -0.88
    1 1 3 1 1 1 1/1/15 -0.06 0 0 0.06
    1 1 6 1 1 1 1/1/15 0.32 0 0 -0.32
    1 1 7 1 1 1 1/1/15 0 0 0 0
    1 1 7 1 1 2 1/1/15 0 0 0 0
    3 1 2 1 1 2 1/1/15 0.02 0 0 -0.02
    3 1 6 1 1 2 1/1/15 2.38 0 0 -2.38
    5 4 7 1 1 2 1/1/15 0 0 0 0
    5 1 5 1 1 5 1/1/15 0.04 0 0 -0.04
    1 1 1 1 1 1 1/2/15 314.42 0 0 -314.42
    1 1 1 1 1 2 1/2/15 7.5 0 0 -7.5
    1 1 2 1 1 1 1/2/15 1.24 0 0 -1.24
    1 1 2 1 1 2 1/2/15 0.76 0 0 -0.76
    1 1 6 1 1 1 1/2/15 0.2 0 0 -0.2
    3 2 28 1 1 2 1/2/15 0.15 0 0 -0.15
    3 1 6 1 1 2 1/2/15 4.88 0 0 -4.88

    Continue reading...

Compartilhe esta Página

Para os pais de Plantão algumas dicas