CODEWITHSUNDEEP
sqlms-access
Vašek Brožek
Vašek Brožek

Reputation: 25

MS Access - string to date

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

Upvotes: 1

Views: 79

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

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.

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

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;

Upvotes: 0

Related Questions