Distinct help ignore timestamp and only return date

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.

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

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

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

Couldn’t you use DISTINCT SUBSTRING(StartDate, 0, 10)

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

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

1 Like

You can also use DATE_FORMAT

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


is returning no results

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

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

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

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.

Sorry i am lost still finding my way around mysql.

i tried

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

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

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

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!!

[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 !!

1 Like

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.)


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.