How can I analyze time length from a text field?

Are you going to be performing this search from code or in SQL?  From code, I would just convert the text into numeric format, turn minutes into seconds and then search for all values less than 30.  If this is a SQL query, I'm not sure if you can do a search for something like WHERE song_length < '00:30' and get your results or not.

You can convert the text field into a datetime field in your query and then do a datediff to get the time duration value.  I have created a sample query below that you can try in SQL and modify as needed to fit within your existing query.

In this example, the 04:30 represents your songlength column that you currently have represented as text in the format of mm:ss.

select *
from (
      select datediff(s,'00:00:00', mins) as songlength
      from (
            select cast('00:' + mins as datetime) mins
            from (select '04:30' as mins) a
            ) b
      ) c
where songlength <= 30

Hope this has helped,

G.

Yep, I'm looking to do a SQL query for this, so gyuksel seems to be on the right page.  Forgive my SQL ineptitude, but can you show me how it would be with my table called "TrackMeta", and the field in question is called "Length".

I tried the code below and it said that the Time field could not be bound

select *
from (select datediff(s,'00:00:00', mins) as songlength
      from ( select cast('00:' + mins as datetime) mins
            from (select TrackMeta.Time as mins) a ) b ) c
where songlength <= 30

Select allOpen in new window

Hi,

Please see below.

select *
from (select datediff(s,'00:00:00', mins) as songlength
      from ( select cast('00:' + mins as datetime) mins
            from (select Length as mins from TrackMeta) a ) b ) c
where songlength <= 30 

Select allOpen in new window

You will obviously want to alter this to bring back more information about the track(s) returned, so you may wish to do something like this:

select *
from (select datediff(s,'00:00:00', mins1) as songlength, *
      from ( select cast('00:' + mins as datetime) mins1, *
            from (select Length as mins, TrackMeta.* from TrackMeta) a ) b ) c
where songlength <= 30 

Select allOpen in new window

Cheers,

G.

Awesome, it's very close.  I think it's bombing on a row that has a Length field that isn't formatted to mm:ss.  Again, it's a text field, so some users may have entered "1minute 20 seconds" or something, and there are definitely a lot of nulls.  Is there a way to make that query ignore all nulls and Length fields that don't format to mm:ss?  Thanks so much!

Oh yea, the SQL error is:
Conversion failed when converting date and/or time from character string.