0

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
4
Views
6 Years
Discussion Span
Last Post by atrueresistance
0

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: n/a

0

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#]
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.