I am trying to convert a time entered in column (TIL_TAKEN) in my table as 00:00 format to Minutes  in order to calculate a new time  CALLED (TIME_LIEU) .Time in lieu would be the result of
 TIL_TAKEN - REMAINING_UNFORMATTED. REMAINING_UNFORMATTED is shown in Minutes so in order to do the calculation TIL_TAKEN must also be in Minutes.
I have tried doing DATEPART(MINUTE,til_taken) AS TIME_LIEU , this keeps giving me 0
Any ideas
My code is attached

FROM         (SELECT     CAST([total week] / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST([total week] % 60 AS varchar(2)), 2) AS TimeInHours, empid, CONVERT(char(5), 
                                              START_MON, 108) AS START_MON, CONVERT(char(5), LUNCH_OUT_MON, 108) AS LUNCH_OUT_MON, CONVERT(char(5), 
                                              LUNCH_BACK_MON, 108) AS LUNCH_BACK_MON, CONVERT(char(5), END_MON, 108) AS END_MON, Mon_morning, Mon_Evening, 
                                              CONVERT(char(5), START_TUE, 108) AS START_TUE, CONVERT(char(5), LUNCH_OUT_TUE, 108) AS LUNCH_OUT_TUE, CONVERT(CHAR(5), 
                                              LUNCH_BACK_TUE, 108) AS LUNCH_BACK_TUE, CONVERT(CHAR(5), END_TUE, 108) AS END_TUE, TUE_morning, TUE_Evening, 
                                              CONVERT(CHAR(5), START_Wed, 108) AS START_WED, CONVERT(CHAR(5), LUNCH_OUT_Wed, 108) AS LUNCH_OUT_Wed, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Wed, 108) AS LUNCH_BACK_WED, CONVERT(CHAR(5), END_Wed, 108) AS END_Wed, Wed_morning, 
                                              Wed_Evening, CONVERT(CHAR(5), START_Thu, 108) AS START_THU, CONVERT(CHAR(5), LUNCH_OUT_Thu, 108) AS LUNCH_OUT_THU, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Thu, 108) AS LUNCH_BACK_THU, CONVERT(CHAR(5), END_Thu, 108) AS END_THU, Thu_morning, 
                                              Thu_Evening, CONVERT(CHAR(5), START_Fri, 108) AS START_FRI, CONVERT(CHAR(5), LUNCH_OUT_Fri, 108) AS LUNCH_OUT_FRI, 
                                              CONVERT(CHAR(5), LUNCH_BACK_Fri, 108) AS LUNCH_BACK_FRI, CONVERT(CHAR(5), END_Fri, 108) AS END_FRI, Fri_morning,CONVERT(CHAR(5), TIL_TAKEN, 108) AS TIL, 
                                              Fri_Evening, CAST(tot_Monday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Monday % 60 AS varchar(2)), 2) AS TOT_MONDAY, 
                                             CAST(tot_Tueday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Tueday % 60 AS varchar(2)), 2) AS TOT_TUEDAY, 
                                              CAST(tot_Wedday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Wedday % 60 AS varchar(2)), 2) AS TOT_WEDDAY, 
                                              CAST(tot_Thuday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Thuday % 60 AS varchar(2)), 2) AS TOT_THUDAY, 
                                              CAST(tot_Friday / 60 AS varchar(7)) + ':' + RIGHT('00' + CAST(tot_Friday % 60 AS varchar(2)), 2) AS TOT_FRIDAY, [total week], 
                                              [total week] - WEEK_HRS AS unformatted_remaining, WEEK_HRS, Month_No, Monday_dates, flex_id AS flex_id, Comment, Confirmed, 
                                              Week_No, Submit,  Manager_Cert, TIME_LIEU
                       FROM          (SELECT     time2.*, tot_Monday + tot_Tueday + tot_Wedday + tot_Thuday + tot_Friday AS [total week]
                                               FROM          (SELECT     time1.*, mon_morning + mon_evening AS tot_Monday, tue_morning + tue_evening AS tot_Tueday, 
                                                                                              wed_morning + wed_evening AS tot_Wedday, thu_morning + thu_evening AS tot_Thuday, 
                                                                                              fri_morning + fri_evening AS tot_Friday
                                                                       FROM          (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, 
                                                                                                                       LUNCH_OUT_MON) AS Mon_morning, DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, 
                                                                                                                      START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, 
                                                                                                                      LUNCH_OUT_TUE) AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, 
                                                                                                                      START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed, END_Wed, DATEDIFF(n, START_Wed, 
                                                                                                                      LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) AS Wed_Evening, 
                                                                                                                      START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu)
                                                                                                                       AS Thu_morning, DATEDIFF(n, LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, START_Fri, 
                                                                                                                      LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning, 
                                                                                                                      DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, CONVERT(VARCHAR, '2085', 3) AS WEEK_HRS, 
                                                                                                                      Month_No, Monday_dates, flex_id, Comment, Confirmed, Week_No, Submit, til_taken, DATEPART(MINUTE,til_taken) AS TIME_LIEU, 
                                                                                               FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime) flexcel

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Something like this:

Open in new window

You can use datediff(), I saw this here on EE last week.


Select datediff(minute, 0, cast('12:30' as datetime))

to see the effect.  This will return 750, which is the number of minutes since midnight.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Yes, folderol is correct that will work as well as it basically creates a datetime stamp at the beginning date a 12:30.  For example on my SQL Server this is 1/1/1900 12:30:00 PM.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kevin CrossChief Technology OfficerCommented:
I like that method!
hi what is the datatype of til_taken
CamnocAuthor Commented:
Kevin CrossChief Technology OfficerCommented:
Is it stored with same date returned by CAST(0 as DateTime) or is each entry stored with datetime when value was saved?


1/1/1900 12:30:00.000 OR 11/1/2008 12:30:00.000
Kevin CrossChief Technology OfficerCommented:
If the former (1/1/1900), then use folderol suggestion - http:#22882211 - like this:

If the latter, then use something like this to find midnight of the day entered and then do the date difference in minutes:

DateDiff(dd, 0, TIL_TAKEN) --> this is what gets you midnight of date specified by TIL_TAKEN
CamnocAuthor Commented:
Thank you all so much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.