MS Access - string to date

Question

I have a column Date with this format: 2019-07-01T07:03:05.612289+02:00

And I need chage thit final form yyyy-mm-ss (in this example 2019-07-01)

Unfortunately this code does nothing:

SELECT Format ([Date], "yyyy / mm / dd")
FROM table_1;

Thaks for tips

Data is stored in ms access table (as string) or in sql-server? — ComputerVersteher
– ComputerVersteher, Commented Aug 29, 2019 at 12:50

Lee Mac · Accepted Answer · 2019-08-29 12:13:04Z

1

I would suggest using a combination of the left and cdate functions to obtain a date value:

select cdate(left([date],10)) from table_1

And then apply any required formatting using the Format property of the field, so that the data remains stored as a date rather than a string.

answered Aug 29, 2019 at 12:13

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arulkumar · Accepted Answer · 2019-08-29 11:04:07Z

0

If your scenario is straigh forward, you can try with LEFT() function:

SELECT LEFT([Date], 10)
FROM table_1;

to convert the same text value into the yyyy-mm-dd date format, use the FORMAT() function:

SELECT FORMAT(LEFT([Date], 10), "yyyy-mm-dd")
FROM table_1;

answered Aug 29, 2019 at 10:51

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

but the result is text not date

The first expression will provide the desired result. The second only adds confusion.