Avatar of JT_SIRO
JT_SIRO
 asked on

How can I analyze time length from a text field?

Due to a design flaw, I have a "Time" textbox field on my asp.net form, where users enter the length of their songs.  This allows them to enter time however they choose, where I should have used validation to force mm:ss.  With that said, I'd estimate that 99% my records are in that format or are null.  

Now I need to query and get all songs under 30 seconds.  Can I do this considering that these values are stored as text in SQL Server?  Also, how do I ignore records that aren't in the proper format or are null?  Thanks
ASP.NETC#.NET Programming

Avatar of undefined
Last Comment
Gorkem Yuksel

8/22/2022 - Mon
nataSofCols

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.
Gorkem Yuksel

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

ASKER
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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gorkem Yuksel

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 

Open 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 

Open in new window



Cheers,

G.
JT_SIRO

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

ASKER
Oh yea, the SQL error is:
Conversion failed when converting date and/or time from character string.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Gorkem Yuksel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question