Solved

converting date and time into datetime in SQL view

Posted on 2013-10-31
7
270 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
  • 4
  • 3
7 Comments
 
LVL 40

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 40

Expert Comment

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

Author Comment

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Citrix policies are the most efficient method to configure and tune XenDesktop environments, allowing organizations to control connection, security and bandwidth settings based on various combinations of users, devices or connection types.  Citrix …
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
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.

831 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