Converting Dates from DateTime

[oseimuduame]

i have my date to be like this 2016-08-16 16:11:52.207. i need to Format my date to give me something like this 2016-08-16, this is because i want to be able to filter using Year, Month and Day instead of Filtering with the Full datetime.

any help please.

[Clever_Anjos · Accepted solution]

//SALES Table

LOAD

  *,

  Date(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_DATE,

  Month(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_MONTH,

  Year(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_YEAR;

SQL SELECT

  o.CreatedDateTime as "SALES_CREATED_DATE",

    o.CreatingUserName as "WAITER",

    o.MenuItemId AS "ITEM_ID",

    o.MenuItemName AS "ITEM_NAME",

    o.Price AS "INVC_PRICE",

    o.Quantity AS "INVC_QTY",

  t.EntityName as "CUST_NAME"

FROM SambaAlata.dbo.Orders o

LEFT JOIN SambaAlata.dbo.TicketEntities t ON o.TicketId = t.Ticket_Id

WHERE EntityTypeId = 1;

[oseimuduame]

i actually Want to Format the date in Item Report. i hope this will work also

[Clever_Anjos]

Which format in which field you need?

[sasikanth]

HI,

Try this,

LOAD *,
 DATE(DayStart(SALES_CREATED_DATE))) as SALES_CREATED_DATE,
YEAR( DATE(DayStart(SALES_CREATED_DATE))) ) as YEAR,
MONTH (DATE(DayStart(SALES_CREATED_DATE))) ) as MONTH;
SELECT
  o.CreatedDateTime as "SALES_CREATED_DATE",
    o.CreatingUserName as "WAITER",
    o.MenuItemId AS "ITEM_ID",
    o.MenuItemName AS "ITEM_NAME",
    o.Price AS "INVC_PRICE",
    o.Quantity AS "INVC_QTY",
  t.EntityName as "CUST_NAME"
FROM SambaAlata.dbo.Orders o
LEFT JOIN SambaAlata.dbo.TicketEntities t ON o.TicketId = t.Ticket_Id
WHERE EntityTypeId = 1;