insert a datetime variable in a timestamp field

Question

I have a problem when I try to insert a date (Now()) in a timestamp declared BigQuery table field. I code in ColdFusion language. In this language, the function Now() returns a datetime of this format: {ts '2014-05-05 15:32:06'}

This is my BigQuery table schema: Name:string,Date:Timestamp

This is my code trying to insert a row:

<cfset row.set("Name","Alex")>
<cfset row.set("Date", now())>

Since the schema in Google BigQuery API does not support date types other than timestamp what should I do to convert the date given by the function now() (in ColdFusion) to be able to insert my row?

Thank you all for your answers, if we have to pass a string variable as a second argument for the method set we can also write:

    <cfset row.set('Date',mid("#now()#",6,19))>

to retreive a string of (yyyy-mm-dd HH:MM:SS)

we can also write That method is a bit brittle. The proper way to convert date objects to strings is with date/timeFormat() as suggested below. Also, nothing to do with your question, but the quotes and # signs around now() are not needed. — Leigh

CFML_Developer CFML_Developer · Accepted Answer · 2014-05-05T13:49:26

You will need to use DateFormat function on now() to make it work. As per bigquery doc, format has to be YYYY-MM-DD HH:MM:SS So you can do something like

EDIT

<cfset row.set("Date", DateFormat(now(),"YYYY-MM-DD")&' ' & TimeFormat(now(),"HH:MM:SS"))>

Again, as I said in comment too, you will need to format your date/time in proper manner in which it is expected.

Thanks to Leigh for pointing it out about dateformat not displaying date & time in single mask

insert a datetime variable in a timestamp field

1 Answers