Combined Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Combined Results

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-24 : 06:39:26

I know this is probably a simple answer for some of you but....

I have two columns I am quering from an MS Access Database. Those columns are named:

Date
Time

When I run the following:

Select [Date], [time]
from Reel_Report
Where bla bla

I get
2005-05-23 00:00:00 12:28

What I would like is:

2005-05-23 00:12:28

combining the results...

Thanks!!

Your limits are only as far as you set your boundries....

<edit> Moved to MS Access forum </edit>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-24 : 11:37:02

If I remember correctly, you can add Date and Time values in Access, so try

SELECT [Date] + [Time]
FROM Reel_Report
WHERE ...

And might I also suggest that you make some plans to change your column names to things that are not reserved words or commands, and in the process are more meaningful like DateCreated, StartTime, etc.

---------------------------
EmeraldCityDomains.com

Hyukevain
Yak Posting Veteran

66 Posts

Posted - 2005-05-28 : 14:46:08

SELECT CONVERT(VARCHAR(10), [DATE], 120) + ' ' + CONVERT(VARCHAR(8), [TIME], 108) AS [DATETIME]
from Reel_Report
Where bla bla

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-31 : 14:01:51

Hyukevain, yes that works in SQL Server, but this question is for MS Access which uses a different syntax. jpiscit1, try the previous answer and let us know if there are any other problems.

---------------------------
EmeraldCityDomains.com

Hyukevain
Yak Posting Veteran

66 Posts

Posted - 2005-06-01 : 01:35:43

In Access syntax :
SELECT Format$([Date],"yyyy-mm-dd") & " " & Format$([Time],"hh:mm:ss") AS [DateTime]
from Reel_Report
Where bla bla

adding 2 field without convert it to string will cause time from [Date] and time from [Time] being added.

AjarnMark, jpiscit1 wants to combine the date from [Date] and time from [Time] into 1 field, not added the time in the 2 field.

If you still want the result type as datetime then use this
SELECT CDate(Format$([Date],"yyyy-mm-dd") & " " & Format$([Time],"hh:mm:ss")) AS [DateTime]
from Reel_Report
Where bla bla

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-06-01 : 14:03:39

If jpiscit1 has been storing only date values in the Date field, and only time values in the Time field, which is what the example shows (and what I have found common practice among Access users), then you can add these two values together to get the specified output.

However, if they have been storing some non-zero time portion in the Date field, then adding them will produce the sum of the times, instead of the concatenation. In which case you are absolutely correct that they need to format and append rather than add.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources