misctp asdas misctp asdas - 4 months ago 19
SQL Question

How to retrieve the Date part out of a Datetime result column?

I have a column of date time which i would like to retrieve only the date. Is there anyway to do this?

Previously it was an epoch value where i convert it to datetime .

Following is a sample result :

smbd|ip address|1082|ip address|"2011-04-26 18:40:34"


I have tried the following commands but has yield negative / zero results

SELECT DATE(datetime) from attacked_total;
SELECT STRFTIME('%Y-%m-%d', datetime) FROM attacked_total;
SELECT DATETIME('%Y-%m-%d', datetime) FROM attacked_total;
SELECT DATE('%Y-%m-%d', datetime) FROM attacked_total;

Answer

You can use the DATE function.

Example

> select date('2011-04-26 18:40:34')
> 2011-04-26

You can get only the day with strftime,

> select strftime('%d', '2011-04-26 18:40:34')
> 26