Distinct help ignore timestamp and only return date

[txt3rob]

I have a table that has a COL called Start Time

all data in this col is filled in like “01/10/2014 10:17:23” i.e date and time stamp.

how can i run something like

SELECT DISTINCT  `Start Time` FROM  `frallmonth`

but i want to only return the dates and ignore the time part.

[DaveMaxwell]

Assuming you are using MySQL, use the DATE function. (reference)

SELECT DISTINCT DATE(`Start Time`) AS StartDate FROM frallmonth

[txt3rob]

it’s not using date unfortunately as the data comes from a CSV imported from a phone system.

[cpradio]

Couldn’t you use DISTINCT SUBSTRING(StartDate, 0, 10)
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring

[DaveMaxwell]

@txt3rob

it’s not using date unfortunately as the data comes from a CSV imported from a phone system.

You didn’t convert the string into a date when you imported the csv into your database?

You can use str_to_date if it’s stored as a string

[cpradio]

You can also use DATE_FORMAT
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

DISTINCT DATE_FORMAT(StartDate, ‘%m/%d/%Y’)

[txt3rob]

SELECT DISTINCT SUBSTRING(  'Start Time', 0, 10 )

is returning no results

[cpradio]

Yeah, that would… As you need to use back ticks, not single quotes to reference a column name with spaces.

SELECT DISTINCT SUBSTRING(  `Start Time`, 0, 10 )

[txt3rob]

@cpradio

DISTINCT DATE_FORMAT(StartDate, ‘%m/%d/%Y’)

SELECT DISTINCT DATE_FORMAT(  'Start Date',  '%m/%d/%Y' )

returns null for some reason but i think i know why as it’s uk date so date month year
the structure is varchar 40

[cpradio]

Again, back ticks to reference column names with spaces (but yes, be sure to replace the %d and %m to reflect the correct order)

[DaveMaxwell]

@txt3rob

returns null for some reason but i think i know why as it’s uk date so date month year

DATE_FORMAT requires a date object, so you’d need to cast the string to date. str_to_date does the same thing, but starts from a string.

[txt3rob]

Sorry i am lost still finding my way around mysql.

i tried

SELECT DISTINCT DATE_FORMAT( 'Start Date', '%e/%m/%Y' ) also returned null

[WolfShade]

Is having a space in a column name legal/valid in any database? (MS-SQL, MySQL, Oracle, etc.)

[DaveMaxwell]

@txt3rob

Sorry i am lost still finding my way around mysql.

Did you see my post? If the column is not a date, date_format won’t work. You’ll either need to CAST the field as a datetime or use str_to_date

SELECT DISTINCT STR_TO_DATE(`Start Time`, '%e/%m/%Y') FROM frallmonth

[txt3rob]

@DaveMaxwell

SELECT DISTINCT STR_TO_DATE(Start Time, ‘%e/%m/%Y’) FROM frallmonth

Sorry did not understand the cast function so will read up a bit more now.

this has now returned the results I was looking for.

SELECT DISTINCT STR_TO_DATE(`Start Time`, '%e/%m/%Y')  AS days FROM frallmonth 

Thank you all for your help!!

[r937]

[quote=“WolfShade, post:13, topic:103004, full:true”]
Is having a space in a column name legal/valid in any database? (MS-SQL, MySQL, Oracle, etc.)
[/quote]yes, legal and valid in all of them

but is it a good idea? NO !!

[WolfShade]

Thanks, @r937. Didn’t know that it was allowed. Assumed it was not a good idea, if it was allowed.

I always use either underscores to replace the spaces, or camelCase. (I prefer camelCase.)