topic Converting Dates from DateTime in QlikView

Converting Dates from DateTime

oseimuduame — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Converting Dates from DateTime

Clever_Anjos — Tue, 30 Aug 2016 13:05:59 GMT

//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;

Re: Converting Dates from DateTime

oseimuduame — Tue, 30 Aug 2016 13:12:36 GMT

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

Re: Converting Dates from DateTime

Clever_Anjos — Tue, 30 Aug 2016 13:15:03 GMT

Which format in which field you need?

Re: Converting Dates from DateTime

sasikanth — Tue, 30 Aug 2016 13:16:01 GMT

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;