CODEWITHSUNDEEP
coldfusiongoogle-bigquery
user3569267
user3569267

Reputation: 1125

insert a datetime variable in a timestamp field

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)

Upvotes: 2

Views: 2090

Answers (1)

CFML_Developer
CFML_Developer

Reputation: 1605

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

Upvotes: 3

Related Questions