Greenwich time

[Anonymous]

Hello,

I downloaded a new file and the date format shows like

2011-05-16 10:33:58 -0400

2011-05-16 12:29:31 -0400

2011-05-16 14:25:00 -0400

2011-05-16 19:30:45 -0400

2011-05-16 20:42:34 -0400

2011-05-17 18:56:56 -0400

2011-05-18 11:22:20 -0400

2011-05-18 16:28:25 -0400

2011-05-19 13:12:39 -0400

How to remove UTC time?

I use

date(timestamp#(created_at, 'M/D/YYYY hh:mm:ss'), 'YYYY-MM-DD') as creationdate,

What's the code for UTC?

Thanks,

[Anonymous]

???

[rwunderlich]

It;s a bit illogical that you are parsing the date & time values but are formatting as date only.

date(timestamp#(created_at, 'M/D/YYYY hh:mm:ss'), 'YYYY-MM-DD') as creationdate

If you want just the date portion, use

date(date#(subfield(created_at,' ',1),'YYYY-MM-DD')) as creationdate

-Rob

http://robwunderlich.com

[Anonymous]

Hi Rob,

It works, but not perfect. I got  date format  5/16/2011.

but it should be YYYY-MM-DD.

Thanks,

[rwunderlich]

The date format you are receiving is the your default format because you are not specifying a format in the date() function. If you want a format other than the default, specify it like:

date(date#(subfield(created_at,' ',1),'YYYY-MM-DD'),'YYYY-MM-DD') as creationdate

-Rob

http://robwunderlich.com

[Anonymous · Accepted solution]

I think the problem was in standard setting QV

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Thanks,