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?

Sqlite **doesn't have** a DATE type. Refer to https://www.sqlite.org/datatype3.html — Shawn, Oct 26 '18 at 00:41
Whatever value you're storing in your purchaseDate column is what it is. If you want it to be `YYYY-MM-DD` that's what you need to store in it and use when comparing/ordering by the column. (Or another recognized date/time format and convert as needed) — Shawn, Oct 26 '18 at 00:44
@Shawn, thanks for pointing that out. That means the database being used is SQL Server. I don't actually have the database, I'm just answering a question. — mrteeth, Oct 26 '18 at 00:57

paulsm4 · Answer 1 · 2018-10-26T00:06:54.080

1

Try using stftime() in your query:

select *
from purchaseOrders
where purchaseDate between strftime(%s, '20-09-18') and strftime(%s, '20-10-18')

The syntax for the strftime function in SQLite is:

strftime(format, timestring [, modifier1, modifier2, ... modifier_n ])

There are also some other alternatives discussed here:

edited Oct 26 '18 at 00:06

answered Oct 26 '18 at 00:00

paulsm4

Thanks for the response. So the first parameter converts 20-09-18 to 2018-09-20? I ask because [in this link](https://www.tutlane.com/tutorial/sqlite/sqlite-strftime-function), it says the second parameter has to be in a certain format, will that be the current format or the format you intend to convert it to? – mrteeth Oct 26 '18 at 00:24
can the query you wrote also be used for SQL Server? – mrteeth Oct 26 '18 at 01:00

Gauravsa · Accepted Answer · 2018-10-26T02:18:06.457

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';

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'

edited Oct 26 '18 at 02:18

answered Oct 26 '18 at 00:04

Gauravsa

Thanks for this Gauravsa. I'll take a look at the link. – mrteeth Oct 26 '18 at 00:26
Thanks again Gauravsa. The first part of you answer is actually okay, because the database is actually being run on SQL Server. – mrteeth Oct 26 '18 at 01:13
Yeah, the convert use a value of 106 (which is dd mon yy), is it possible to have it as 120 (yyyy-mm-dd hh:mi:ss), with the " hh:mi:ss" removed? If so, how do you do it? – mrteeth Oct 26 '18 at 01:18
Okay, I see. Thanks a lot for the help Gauravsa. One last thing, do I not need the REPLACE clause? – mrteeth Oct 26 '18 at 02:24
If you simply need '2012-06-18' then 120 gives you, no need of replace. – Gauravsa Oct 26 '18 at 02:28

2 Answers2