Re: easiest way to coerce "?m/?d/yyyy" strings to a date in monetdb?

On Mon, May 18, 2015 at 08:38:03AM -0400, Anthony Damico wrote:

thanks Niels!  so you are saying this command should work, correct?  it currently does not..

"SELECT str_to_date( '1/1/2014' , '%m/%d/%Y' ) as date"

if i am on windows, do i need to install strptime separately?  i am currently hitting this error that (from the version tracker) seems like it should've been resolved? on windows this maybe a problem indeed. strptime etc support doesn't exist on windows.

Niels

Error in .local(conn, statement, ...) :   Unable to execute statement 'SELECT str_to_date( '1/1/2014' , '%m/%d/ %Y' ) as date'. Server says '!strptime support missing'.

http://permalink.gmane.org/gmane.comp.db.monetdb.scm/36127

thank you  :)

On Mon, May 18, 2015 at 8:09 AM, Niels Nes wrote:

On Mon, May 18, 2015 at 07:17:28AM -0400, Anthony Damico wrote: > hi, i have a VARCHAR column with dates like > > "1/1/2014"

Anthony

We have the following date/string conversion functions

create function str_to_date(s string, format string) returns date         external name mtime."str_to_date";

        create function date_to_str(d date, format string) returns         string                 external name mtime."date_to_str";

                create function "convert"(s string, format string)                 returns date                         external name mtime."str_to_date";

Its based on the unix strftime/strptime (so for the format look at those).

Niels > > and also > > "12/31/2014" > > since there are no leading zeroes, the months and days are in different > positions so i cannot use something like this to CAST it into a date > type. > > > CAST( SUBSTRING( x , 7 , 10 ) || '-' || SUBSTRING( x , 1 , 2 ) || '-' | > | SUBSTRING( x , 4 , 5 ) AS DATE ) AS y > > > could someone offer advice to convert this string field to date in > monetdb?  thanks!!

> _______________________________________________ > users-list mailing list > users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14,  phone ++31 20 592-4098     sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels    e-mail: Niels.Nes@cwi.nl

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl