CODEWITHSUNDEEP
sqlite
junkone
junkone

Reputation: 1465

How to proper cast in sqllite

When i cast datetime in SQLLite, it truncates the string. for example

select cast("2017-04-23 9:12:08 PM" as datetime) as dt

returns

2017

Upvotes: 0

Views: 349

Answers (2)

MikeT
MikeT

Reputation: 56953

SQLite's CAST can only cast to the defined storage classes and can therefore only be used to cast to

NONE (blob), TEXT, REAL, INTEGER or NUMERIC.

However the normal rules for determing column-affinity are applied to the type so by coding CAST(value AS datetime) you are effectively using CAST(value AS NONE) (i.e. a BLOB).

CAST expressions

Therefore you can't effectively use CAST. However you simply use the DateTime functions against an appropriate value (accepted formats) as per Date And Time Functions e.g. :-

SELECT datetime("2017-04-23 09:12:08") as dt;

results in 

2017-04-23 09:12:08

or to show date manipulation 

select date(dt), dt FROM (
    select datetime("2017-04-23 09:12:08") as dt
);

results in 

2017-04-23

and

2017-04-23 09:12:08

However considering that your format isn't one of the accepted formats you could convert the value. This is more complex but it can be done. Here's an example that will perform the conversion (not substantially tested though) :-

SELECT
    CASE WHEN (CAST(hour AS INTEGER) + CAST(adjustment AS INTEGER)) > 9  THEN 
            datepart||' '||CAST(CAST(hour AS INTEGER) + CAST(adjustment AS INTEGER) AS TEXT)||':'||mins_and_secs
        ELSE
            datepart||' 0'||CAST(CAST(hour AS INTEGER) + CAST(adjustment AS INTEGER) AS TEXT)||':'||mins_and_secs
    END AS converted
FROM (
    SELECT substr(ts,1,10) as datepart, 
        CASE WHEN instr(ts,"PM") THEN 12 ELSE 0 END AS adjustment,
        CASE WHEN length(ts) = 21 THEN substr(ts,12,1) ELSE substr(ts,12,2) END AS hour,
        CASE WHEN length(ts) = 21 THEN substr(ts,14,5) ELSE substr(ts,15,5) END AS mins_and_secs
    FROM (
        select("2017-04-23 9:12:08 PM") as ts
    )
);

This would result in 2017-04-23 21:12:08.

Using select("2017-04-23 9:12:08 AM") results in 2017-04-23 09:12:08

Using select("2017-04-23 11:12:08 PM") results in 2017-04-23 23:12:08

Using select("2017-04-23 11:12:08 AM") results in 2017-04-23 11:12:08

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300549

The closest I could come up with is:

select date(datetime(strftime('%s','2017-04-23 09:12:08'), 'unixepoch'))

Result:

2017-04-23

The dateformat you have is not recognised by SQLite:

"2017-04-23 9:12:08 PM"

It does not conform to the Time string formats recognised:

A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Date And Time Functions

Upvotes: 0

Related Questions