Solved

converting date and time into datetime in SQL view

Posted on 2013-10-31
7
276 Views
Last Modified: 2013-11-02
I am creating a view in SQL and have a date and time field that are separate as a string and want to make them a single datetime value.

Current Format

Separate columns in table:

Date:
1912-11-26    

Time:
10:20:00

desired format in new column:
2012-12-13 13:12:00

Thanks for any assistance
0
Comment
Question by:srodgers45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39615997
How do you convert 1912-11-26 to 2012-12-13?
and 10:20:00 to 13:12:00?
0
 

Author Comment

by:srodgers45
ID: 39616012
That was just an example of the format I need, not the actual data.

Currently I have the date in one column and the time in another.

The date is a string (varchar) not a datetime type.

I need to combine the date and the time into a single datetime data field in the the view.

Thanks
0
 

Author Comment

by:srodgers45
ID: 39617469
This is the statement in the view:

I need to combine the Scene Date and Scene Time into a single datetime column in the view.

SELECT     dbo.FDC_Trips.tdate, dbo.Companies.name AS Agency, dbo.FDC_Trips.veh AS Unit, dbo.FDC_Trips.RunNumber AS [Incident #],
                      dbo.FDC_Trips.outcomedescr AS Outcome, dbo.FDC_Trips.IncidentNumber1 AS [RMS #], dbo.FDC_Trips.g2pcrid AS [PCR ID],
                      dbo.v_FDC_MiscFields.UserField15Value AS [Service Level], dbo.FDC_Trips.completedTime AS [Comp. Time], dbo.Drivers.name AS [Completed By],
                      dbo.FDC_Trips.atsdate AS [Scene Date], dbo.FDC_Trips.atstime AS [Scene Time]

View - Column
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 39618084
What is your database?
0
 

Author Comment

by:srodgers45
ID: 39618125
SQL 2008R2
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39618485
can you check if this works
SELECT     dbo.FDC_Trips.tdate, dbo.Companies.name AS Agency, dbo.FDC_Trips.veh AS Unit, dbo.FDC_Trips.RunNumber AS [Incident #], 
                      dbo.FDC_Trips.outcomedescr AS Outcome, dbo.FDC_Trips.IncidentNumber1 AS [RMS #], dbo.FDC_Trips.g2pcrid AS [PCR ID], 
                      dbo.v_FDC_MiscFields.UserField15Value AS [Service Level], dbo.FDC_Trips.completedTime AS [Comp. Time], dbo.Drivers.name AS [Completed By], 
                      convert(datetime,dbo.FDC_Trips.atsdate + ' ' + dbo.FDC_Trips.atstime) AS [Scene Time]

Open in new window

0
 

Author Closing Comment

by:srodgers45
ID: 39619168
Thank You. That worked...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After several days of searching and hunting for limited documentation, I wanted to share this guide to hopefully save someone the hassle of trying to figure this out on their own. I have tested this on Xendesktop 7.1 and PS 4.5 running simultaneous…
Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

632 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