CONVERT DATETIME TO MINUTES

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

SELECT     flexcel.*,  UNFORMATTED_REMAINING
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, 
                                                                                                                      Manager_Cert
                                                                                               FROM          dbo.EMPLOYEE_TIMESHEET) Time1) time2) FinalTime) flexcel

Open in new window

CamnocAsked:
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:
(CAST(LEFT(TIL_TAKEN, CHARINDEX(':',TIL_TAKEN)-1) AS INT) * 60 + CAST(RIGHT(TIL_TAKEN, CHARINDEX(':', REVERSE(TIL_TAKEN))-1) AS INT) - REMAINING_UNFORMATTED) AS TIME_LIEU

Open in new window

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

Try

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.

0

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
I like that method!
0
kerwinsiyCommented:
hi what is the datatype of til_taken
0
CamnocAuthor Commented:
TIL_TAKEN IS DATATYPE  Datetime
0
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?

i.e.

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

If the latter, then use something like this to find midnight of the day entered and then do the date difference in minutes:
SELECT DATEDIFF(minute, DateDiff(dd, 0, TIL_TAKEN), TIL_TAKEN)

DateDiff(dd, 0, TIL_TAKEN) --> this is what gets you midnight of date specified by TIL_TAKEN
0
CamnocAuthor Commented:
Thank you all so much.
Excellent
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.