Solved

Access SQL - combine Date and Time into one field for query

Posted on 2014-10-02
12
178 Views
Last Modified: 2016-06-25
When I design a database I use datetime fields and put both date and time in the field.  Someone else designed the one I'm working with and even though both fields are datetime type, one has the date and the other has the time.

VIA Access SQL only, how can I combine the two into one "field" - I've tried "DateField + TimeField" but it just prompts me for a value.  I think it might be something with CAST or CONVERT if this were T-SQL...

I would think this is easy and quick... anyone have any ideas?

Thanks.
0
Comment
Question by:Lee W, MVP
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 21

Expert Comment

by:mcsween
ID: 40357535
I suspect you will have to use the DatePart function then concatenate the values together.

http://www.techonthenet.com/access/functions/date/datepart.php
http://www.techonthenet.com/access/functions/string/concat.php
0
 
LVL 84
ID: 40357587
You can use DateSerial and TimeSerial:

SELECT DateSerial(Year(YourDate), Month(YourDate), Day(YourDate) & " " & TimeSerial(Hour(YourTime), Minute(YourTime), Second(YourTime)) AS YourField FROM YourTable

You may need a slightly different syntax:

SELECT DateSerial(Year(YourDate), Month(YourDate), Day(YourDate) & "T" & TimeSerial(Hour(YourTime), Minute(YourTime), Second(YourTime)) AS YourField FROM YourTable

Or

SELECT DateSerial(Year(YourDate), Month(YourDate), Day(YourDate) & ":" & TimeSerial(Hour(YourTime), Minute(YourTime), Second(YourTime)) AS YourField FROM YourTable

If the Fields are Text fields, you might need to enclose them in CDate:

SELECT DateSerial(Year(CDate(YourDate)), Month(CDate(YourDate)), Day(CDate(YourDate)) & "T" & TimeSerial(Hour(YourTime), Minute(YourTime), Second(YourTime)) AS YourField FROM YourTable
0
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40357670
That looked promising but I tried it and with JUST the date it wants

...
DateSerial(
Year(WorkOrder.Start_dt),
Month(WorkOrder.Start_dt),
Day(WorkOrder.Start_dt)
) AS OrderingTime
FROM ...

I get prompted for a value
Prompt
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40357721
Actually, I would suspect that you could simply use dateValue and TimeValue functions and sum them together:

DateValue([DateField]) + TimeValue([TimeField])
0
 
LVL 21

Expert Comment

by:mcsween
ID: 40357738
DateSerial(
Year(WorkOrder.Start_dt),
Month(WorkOrder.Start_dt),
Day(WorkOrder.Start_dt)
) AS "OrderingTime"
FROM ...

Note the quotes around OrderingTime.  This expresses a literal instead of a variable; that's why Access is prompting.
0
 
LVL 84
ID: 40358005
You don't need quotes around the OrderingTime ... if anything, you'd use Square Brackets.

Lee: Can you show the full syntax of your query?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40358091
This is way off. [DateFieldName] + [TimeFieldName] is the direct method to use.

If that prompts you for a value, you have a misspell or not using the actual field names.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41666008
The answer is here: https:#a40358091

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41666917
and also here
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41666966
But Dale, what is the purpose of "converting" a date value to a date value and likewise for the time value? It doesn't seem to add anything except unneeded complexity.

/gustav
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question