?
Solved

SQL - convert hhmm to datetime

Posted on 2011-04-28
1
Medium Priority
?
629 Views
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

BINTVWS.Time_from
1400
1000
930

what is the SQL code to display the above as
14:00
10:00
9:30

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.
0
Comment
Question by:JCTDD
1 Comment
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 35489097

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

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 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