Change the format of date column

Question

I have a column called purchaseDate of type DATE. I want to write a query that returns purchaseDate in YYYY-MM-DD format.

I'm having trouble doing this while using a between function. Here's my code

select *
from purchaseOrders
where purchaseDate between '20-09-18' and '20-10-18'

This is the result I expect to get:

purchaseDate
------------
2018-09-20
2018-09-21
2018-09-22
2018-10-19
2018-10-20

How can I do this?

Answer 1

You can simply do this:

select CONVERT(varchar(10), purchaseDate, 120) AS 'Purchase Date'
from purchaseOrders
where purchaseDate
between '20-09-18' and '20-10-18';

More here: How to get a date in YYYY-MM-DD format from a TSQL datetime field?

Edit 2:

SQLite requires date to be in YYYY-mm-dd format or other recognizable formats in this post: SQL Select between dates

You can simply do this:

select *
from purchaseOrders
where purchaseDate
between '2018-09-18' and '2018-10-18'