SQL Convert / Cast (Datetime), format?

Question

Hello,

I am very new to sql and having trouble understanding the syntax of the cast and convert function when combined with select and multiple columns from a table.

My current query which is from an ODBC connection which may be a factor based on my reading is below:

I simply want to CAST or CONVERT the column "plant_job.job_issued_date" to a Datetime format YYYY-MM-DD-HH-SS

I have also been trying the SELECT DECALRE FORMAT with no success.

Any help appreciated.

Cheers

let

    Source = Odbc.Query("dsn=Database", "SELECT plant_job.plt_job_no, plant_job.plt_job_type, plant_job.plt_job_status, plant_job.plant_no, plant_job.scheduled_date, plant_job.job_issued_date, plant_job.finished_date, plant_job.plt_sort_date, plant_job.job_plant_dept, plant_job.labour_type_1, plant_job.planned_by_code, plant_job.next_action_by, plant_job.plt_job_priority, plant_job.job_approved_by, plant_job.approved_date, plant_job.job_section_code#(lf)FROM pronto.plant_job plant_job")

Answer 1

Ok, the first thing is that if the sql column is a datetime (and NOT datetime2), then a linked table to that sql server should return the column as date/time.

As a result, you don't need to convert, or cast or do anything. In Access, a date column is simply a date column, and are you are free to use any format in a form or report to display that column as a date. 

Even if you use a format that results in say May 02, 2010, despite the month showing first, it will STILL sort by date order.

So, you don't need to cast in the query. Simply apply a format in the form, or report. (this means that any user can have a different format. If you try and force the format from the server query, then you lose the ability to format and sort in access.

However, VERY important to check the access linked table in design mode. (ignore the ready only warning). Now, look at the data type access assigned to the columns. If the datetime column appears as TEXT, then you NEED to fix this issue.

You want to check the column data type on sql server. If it is a datetime2 column, then Access will see this as a TEXT column, you have a HUGE mess on your hands. The solution is twofold:

If you have control over the sql server database, then change the column from a datetime2 to a datetime column, and re-link your tables.

Or, install a newer sql driver on your computer. Native 11 (or later). If you install a newer driver, and re-link the access tables using this newer driver, then Access WILL SEE the column(s) as date, and again you don't need to cast the query into a date column.

So, use datetime columns in place of datetime2 columns (if possible).

If not possible, then link using a newer ODBC driver, and they have support for the newer date time formats that SQL server uses. 

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada