Type conversion in U-SQL

Question

I am very new to USQL and wondering how to cast a "datetime" to "date" in a select statement. Also, how do i get rid of the millisecond and am/pm? I'd really appreciate any help on this. Thank you all.

Answer 1

Below is the code that works.Notice the parenthesis.

@date =
SELECT (datevalue).ToString("MM-dd-yyyy") AS date
FROM @datetime;

Answer 2

You can use inline C# to do that.

@rowset = SELECT dateTimeColumn.Date AS dateOnly FROM @anotherrowset;

To get rid of values you can use the dateTime.ToString(format), e.g. dateTime.ToString("mm/dd/yy hh:mm).

Answer 3

Interesting, I couldn't get the .Date working either, will look into it. In the meantime, using the .ToShortDateString() works. .ToString works as well.

E.g. code

@datetime =
SELECT *
FROM(
    VALUES
    (
        DateTime.Now
    ),
    (
        new DateTime(2016, 05,31)
    ),
    (
        new DateTime(2015, 01, 01)
    )) AS v(datevalue);


OUTPUT @datetime
TO "/output/datetime.txt"
USING Outputters.Text();

@date =
    SELECT datevalue.ToShortDateString() AS date
    FROM @datetime;

OUTPUT @date
TO "/output/date.txt"
USING Outputters.Text();

Alternate:

@date =
SELECT datevalue.ToString("MM-dd-yyyy") AS date
FROM @datetime;