Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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
0
srodgers45
Asked:
srodgers45
  • 4
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Industry Leaders: 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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now