Member Avatar for atrueresistance

Hello All I'm converting an access database to MS SQL for a webapp and have a question if anyone knows how to do this.

4 fields [TimeReported] [TimeOff] [TimeOn] [TimeAssigned]

Access only stored time, so date is incorrect. 1 Field stored date separately [Date] I would like to take each date from [Date] and place in the 4 fields while keeping the Time in the 4 fields.

I'm guessing it is has something to do with concat and possibly splitting. I'm not used to MS SQL very well, which is why I'm asking.

Example
[TimeReported] = 12/30/1899 10:00 PM
[TimeOff] = 12/30/1899 6:00:00 PM
[TimeOn] = 12/30/1899 8:00:00 PM
[TimeAssigned] = 13/30/1899 6:00:00 PM
[Date] = 1/21/2010 10:00:00 PM

I want
[TimeReported] = 1/21/2010 10:00 PM
[TimeOff] = 1/21/2010 6:00:00 PM
[TimeOn] = 1/21/2010 8:00:00 PM
[TimeAssigned] = 1/21/2010 6:00:00 PM

And then I can remove [Date] Field.
[Date] = 1/21/2010 10:00:00 PM

  • 1 Contributor
  • 2 Replies
  • 130 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by atrueresistance
Member Avatar for atrueresistance

I've Figured out the Select statement, now just need to figure out how to update

SELECT 
      [TimeReported] + [Date] as Combined
     
  FROM [C:\POWERWEBAPP\APP_DATA\DATABASE.MDF].[dbo].[Table1], [C:\POWERWEBAPP\APP_DATA\DATABASE.MDF].[dbo].[Date]
  where [Date].[Report#] = [Table1].[Report#]
Edited by atrueresistance because: n/a
Member Avatar for atrueresistance

So I figured it out.

UPDATE 
	[table1] 
SET
	[table1].[TimeReported] = [TimeReported] + [Date]

FROM [C:\POWERWEBAPP\APP_DATA\DATABASE.MDF].[dbo].[Table1], [C:\POWERWEBAPP\APP_DATA\DATABASE.MDF].[dbo].[Date]
  where [Date].[Report#] = [Table1].[Report#]
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.