converting date and time into datetime in SQL view

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
srodgers45Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
How do you convert 1912-11-26 to 2012-12-13?
and 10:20:00 to 13:12:00?
0
 
srodgers45Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
srodgers45Author Commented:
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
 
SharathData EngineerCommented:
What is your database?
0
 
srodgers45Author Commented:
SQL 2008R2
0
 
srodgers45Author Commented:
Thank You. That worked...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.