How can I truncate the time off my dates that are coming in the format 5/03/2016 12:00:00 AM

[#1]

I am working to come up the learning curve

[sunny_talwar]

Use the Floor() function:

If TimeStamp is read properly, you can try this

Date(Floor(FieldName)) as Date

If its not read as timestamp (and is text), then this:

Date(Floor(TimeStamp#(FieldName, 'M/DD/YYYY hh:mm:ss TT'))) as Date

[#3]

Sunny, You are an enormous help!  My next dumb question is where would I put this?

SQL SELECT

"HOST_ORDER",

"ORDER_NUM",

"CUST_NUM",

"SHIP_TO",

    "DATE_CREAT",

    "DATE_ORDER",

    "DATE_CANCL",

    "DATE_REQD"

FROM "A1Warehouse"."dbo"."PICKHEAD";

[sunny_talwar]

Add a load statement right above your SQL

LOAD *,

Date(Floor(TimeStamp#(FieldName, 'M/DD/YYYY hh:mm:ss TT'))) as Date;

SQL SELECT

"HOST_ORDER",

"ORDER_NUM",

"CUST_NUM",

"SHIP_TO",

    "DATE_CREAT",

    "DATE_ORDER",

    "DATE_CANCL",

    "DATE_REQD"

FROM "A1Warehouse"."dbo"."PICKHEAD";

Not sure which of the four dates are we trying to fix, but all should be fixed the same way

[#5]

Date was returned empty.  Thoughts?

LIB CONNECT TO [truco-ac1];
[PICKHEAD]:

LOAD *,

Date(Floor(TimeStamp#("DATE_CREAT", 'M/DD/YYYY hh:mm:ss TT'))) as Date;

SQL SELECT
"HOST_ORDER",
"ORDER_NUM",
"CUST_NUM",
"SHIP_TO",
    "DATE_CREAT",
    "DATE_ORDER",
    "DATE_CANCL",
    "DATE_REQD"
FROM "A1Warehouse"."dbo"."PICKHEAD";

[sunny_talwar]

Run just this:

LIB CONNECT TO [truco-ac1];
[PICKHEAD]:

SQL SELECT
"HOST_ORDER",
"ORDER_NUM",
"CUST_NUM",
"SHIP_TO",
    "DATE_CREAT",
    "DATE_ORDER",
    "DATE_CANCL",
    "DATE_REQD"
FROM "A1Warehouse"."dbo"."PICKHEAD";

and create a filter box for DATE_CREAT and take a screenshot to share with us here

[#7]

Here you go...thanks!

[sunny_talwar]

How about this (Removed one D from the date format - even that little details can throw the results off )

LIB CONNECT TO [truco-ac1];
[PICKHEAD]:

LOAD *,

Date(Floor(TimeStamp#("DATE_CREAT", 'M/D/YYYY hh:mm:ss TT'))) as Date;

SQL SELECT
"HOST_ORDER",
"ORDER_NUM",
"CUST_NUM",
"SHIP_TO",
    "DATE_CREAT",
    "DATE_ORDER",
    "DATE_CANCL",
    "DATE_REQD"
FROM "A1Warehouse"."dbo"."PICKHEAD";

[swuehl]

You could probably also have the DBMS transform the datetime to date.

For example, for a recent MS SQL, try something like

SQL SELECT

"HOST_ORDER",

"ORDER_NUM",

"CUST_NUM",

"SHIP_TO",

    CAST("DATE_CREAT" AS DATE),

    "DATE_ORDER",

    "DATE_CANCL",

    "DATE_REQD"

FROM "A1Warehouse"."dbo"."PICKHEAD";

Maybe this does not work in your environment, but I am pretty sure there is a way to achieve this transformation, ask your local DB guru.

[swuehl]

You can also try without the timestamp#() interpretation function like suggested in Sunny's first post, assuming QV will get a numeric representation from the DBMS:

LIB CONNECT TO [truco-ac1];
[PICKHEAD]:

LOAD *,

Date(Floor("DATE_CREAT")) as Date;

SQL SELECT
"HOST_ORDER",
"ORDER_NUM",
"CUST_NUM",
"SHIP_TO",
    "DATE_CREAT",
    "DATE_ORDER",
    "DATE_CANCL",
    "DATE_REQD"
FROM "A1Warehouse"."dbo"."PICKHEAD";