[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Data Conversion

Posted on 2007-10-15
8
Medium Priority
?
542 Views
Last Modified: 2013-11-30
Hi All,
please assist on following conversion in SQL Query
20071011 to 11-10-2007 as date (single column data)
and 102311 to 10:23:11 as time (single column data)
0
Comment
Question by:batman_k
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20080082
SELECT CONVERT(VARCHAR(10),CAST('20071011' AS DATETIME), 110)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20080267
For the second part

SELECT LEFT(urColumn,2) + ':' + SUBSTRING(urColumn, 3,2) +':'+RIGHT(urColumn,2)
0
 
LVL 2

Author Comment

by:batman_k
ID: 20080755
Hi Aneesh,
Please help with Date part.
Column is called StartDate and data varies (is not always as 20071011)
Please send example
For time, comumn is called StartTime. Please send example

Thanks in advance!
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20082834
you just need to replace that value with your column name


SELECT CONVERT(VARCHAR(10),CAST(StartDate AS DATETIME), 110)
from YourTable
0
 
LVL 2

Author Comment

by:batman_k
ID: 20086104
Hi Annesh,

Both examples are working...thanks!
One last thing on Time:
Time is 092010 for example. Conversion needs to make it as 09:20:10
example is working for 142320 aka 14:23:20

Thanks!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20086256
SELECT LEFT('092010',2) + ':' + SUBSTRING('092010', 3,2) +':'+RIGHT('092010',2)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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