SQL - convert hhmm to datetime

Posted on 2011-04-28
Last Modified: 2012-05-11
using SQL server 2000

I have a time field that is a char(10) I want to be able to convert it to datetime so can get the semicolon between then hours and mins


what is the SQL code to display the above as

using this: CONVERT(VARCHAR(5), cast(BINTVWS.Time_from as datetime), 108) as interviewTimeStart

get err:
Server: Msg 242, Level 16, State 3, Line 13
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Question by:JCTDD
    1 Comment
    LVL 23

    Accepted Solution


    declare @time varchar(10)
    set @time = '930'
    select substring(@time, 1, len(@time) - 2) + ':' + substring(@time, len(@time) - 1, len(@time))

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now