Sill new to Bubble. Following another post on the forum I was able to successfully convert a text date to a date in bubble and display this on a screen using an expression and it works great.
I have another situation where I’m using a workflow and reading a SQL record, then in then next step I’m updating a local Bubble database with that data. But, the SQL table has a text date field that I need to update a date field in the bubble DB. I can’t figure out how to incorporate an expression to do this prior to updating the table or while updating the table in a workflow. The expression editor in the “Make Changes to a” (in this case a record in the Bubble DB) forces me to use predefined expressions options and doesn’t allow me to type a text expression using Java. I tired to figure out how to calculate the date in an expression prior workflow step and use the results but I can’t figure out how to even do that. How can I convert a text date to a date in a workflow?
For things like this, I just solve in SQL if at all possible. Then you just map the data type in Bubble and you’re off to the races.
Not sure what SQL DB you are using, but in MySQL, there is a function to convert strings to dates:
SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’)
Just change your query in the Bubble SQL Connector to use that function (or a similar one if you’re not on MySQL), map it as a Date data type in Bubble, and you should be good to go.
@joeyg That makes a lot of sense. I’m using Microsoft SQL but I can’t figure out how to convert text to date and change the format at the same time. Bubble doesn’t like the default date format in SQL which is yyyy-mm-dd. I can use Cast(textdate as DATE) and get a yyyy-mm-dd date but Bubble gives an error converting it, it works in SQL. I assume it is because the date format is incorrect. I tried to do a Covert on the date to another format such as mm/dd/yyyy but I can’t get it to work in SQL.
Try creating a test case first by just displaying the date onto a screen and use the “formatted as” option when displaying the date. For example, I use a custom format of ddd mmm d h:MMtt, which displays like Tue Aug 15 4:00pm. I would guess yyyy-mm-dd should work too.
If you can get it to display properly, then you know Bubble has recognized/parsed it correctly. From there, you can figure out how to add it to the Bubble database.
I agree with @joeyg it is worth persevering with getting Bubble to accept it in date format. Bubble should accept ISO-8601 format, see this …
Well done on figuring out a solution!
@jpayne what does the workflow steps look like? What event starts the JS workflow? what is the JS script? Can you also show where it is being written to the db?
An alternative to putting the write to DB in a “js to bubble event workflow” is putting it in a custom event, and schedule it for example 0.1 secs. This ensures the sequence, and the custom event can be passed parameters.
@jpayne, if you can post your SQL query, Bubble mapping, and db update, maybe I can figure it out.
@joeyg, I think it would work fine if I was using a SQL date field. The MS SQL Database I’m reading was setup with text fields for dates that store a text date formatted as yyyymmdd. I don’t believe Bubble will read that into a date field. It’s the same as if I was reading a yyymmdd date field from JSON I believe. Just text that has to be converted to a field type.
@mishav. This is all being performed in a workflow when the user logs into the application. The workflow steps I tried to use that didn’t work is:
- Read User Profile - uses Bubble SQL DB Connector to read MS SQL table for the user profile data including the users text yyyymmdd birthdate field.
What I had to do to get it to work was:
- Read user Profile (from SQL DB)
- Make changes to a profile (write all SQL DB fields to bubble from step 1 except the DOB field)
Hope that made sense.