Solved

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

Posted on 2014-10-02
12
191 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 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