CODEWITHSUNDEEP
sqlstringsqlitedatetime
user7483931
user7483931

Reputation:

Converting date time value to 10 character date on sql

I'm trying to use the below code to convert the 'date_time' values in my table to 10 characters. Currently, the date_time values are in the format '2020:09:08 10:00:00' but I want to get rid of the time portion of the values so that I'm only left with the date. I'm using DB Browser for SQLite and I thought I would have to use the varchar data type to complete this task but its not working. The output of the code is also shown below.

SELECT CAST(date_time AS VARCHAR(10)) FROM stocks;

enter image description here

Upvotes: 0

Views: 1365

Answers (2)

forpas
forpas

Reputation: 164089

Your datetime values are not valid datetimes for SQLite which recognizes only the format 'YYYY-MM-DD hh:mm:ss'.
If you used the above format then all you need would be the function DATE():

SELECT DATE(date_time) 
FROM stocks;

For your current format you must use string functions to extract the first 10 chars and then replace all : with -:

SELECT REPLACE(SUBSTR(date_time, 1, 10), ':', '-') 
FROM stocks;

Upvotes: 0

GMB
GMB

Reputation: 222432

I would just use string functions:

substr(date_time, 1, 10)

Upvotes: 1

Related Questions