Converting a VARCHAR into DATETIME format

Posted on 2007-07-23
Last Modified: 2009-07-29
Stumped on this...I am trying to do some reporting off of a badly designed database.

I have a column called 'Hours'.  The column is VARCHAR (10) and the data is formatted 00:00

Since it is VARCHAR I am unable to sum up the total grouped by employee.

I am looking to take the column 'Hours' and convert from VARCHAR to DATETIME.  I have tried REPLACE, CONVERT, SUBSTRING and I guess I am just not getting the right combination of syntax.  I have tried to strip the column into two columns and sum hours and minutes but then I would need some time of calculation to rollover 60 minutes as an hour...too confusing.

So visually I need this

Question by:PROJHOPE
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello PROJHOPE,

    Try this:

    SELECT EmpID, SUM(24 * CONVERT(decimal(19, 6), CONVERT(datetime, Hours))) AS DecimalWorkHours
    FROM YourTable

    For example, if the total working time were 20 hours, 15 minutes, should return 20.25.



    Author Comment

    Hi Patrick,

    Tried your script but didn't get the desired results:

    Using a real example:

    Hours column


    using the convert script brought back

    15.000000 in the DecimalWorkHours calculated column

    If an employee has this:

    Day 1 07:30
    Day 2 07:45
    Day 3 08:53

    It would be nice to first convert to decimal then I can sum the column

    something like a case statement

    Case :30 THEN .15
    :45 THEN .75
    but then you have those that have :53, :42, :33 and so forth that could make this difficult.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    OK, trying again :)

    SELECT EmpID, SUM(CONVERT(decimal(9, 4), LEFT(Hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(Hours, 2)) / 60)) AS TotHrs
    FROM YourTable
    LVL 1

    Expert Comment

    declare @test varchar(10)
    declare @date datetime,
          @newdate datetime

    set @date = getdate()
    set @test = '07:45'

    select cast(convert(varchar(10),@date,101) + ' ' + @test as datetime)  as ConvertedDate
    select @newdate = cast(convert(varchar(10),@date,101) + ' ' + @test as datetime)

    select datediff(hh,getdate(),@newdate)


    I would make the above into a function to make your life easier since you will be combining the date field and the text field often to make a datetime.

    Author Comment


    Using the new code...same results:

    the TotHrs calculated column shows at 15.0000 for 07:30

    DotNetBuddha, One question, the query is a SELECT statements so if I put the DECLARE above it how will I be able to put the column 'hours' as the @test field parameter?

    I cannot put set @test = A.hours because the column nor its table has yet to be defined.


    Author Comment

    I stand corrected...matthewspatrick your code works sorry for doubting you.

    If I create a simple script:

    select *,SUM(CONVERT(decimal(9, 4), LEFT(Hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(Hours, 2)) / 60)) AS TotHrs from timecard_raw
    where serial_number = '000002074'
    and pay_period = '2006/08/31'
    group by serial_number,pay_period,sequence,detail_date,timecode_id,ld_code1,ld_code2,ld_code3,ld_code4,

    It works!!!

    But when I put it in my Select statement it doesn't...perhaps you can see where my statement is off...thanks again...

    CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
    A.ld_code3 AS 'Project Code',
    A.hours AS 'Hours Non-Formatted',
    SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted',
    MAX(DATEADD (mi,B.audit_timestamp,'01/01/1930')) AS 'Employee Timestamp'
    Ceridian.dbo.timecard_raw AS A INNER JOIN
    Ceridian.dbo.employee AS B ON A.serial_number = B.serial_number
    (A.serial_number = '000002074' AND B.last_name IS NOT NULL AND
    B.middle_name IS NOT NULL AND
    B.first_name IS NOT NULL)
    GROUP BY A.serial_number, A.pay_period, A.detail_date,A.sequence, A.timecode_id,
    A.ld_code3, A.hours, B.last_name,B.middle_name, B.first_name
    LVL 92

    Accepted Solution

    PROJHOPE said:
    >>I stand corrected...matthewspatrick your code works sorry for doubting you.

    That's a relief.  I had just gone and created a test scenario in SQL Server, and could not
    understand why it was not working for you when it worked for me every time  :)

    I do not see anything obviously wrong with your SQL.  My guess is that either you need
    to refine the join, and/or refine the WHERE conditions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now