Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-02
12
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 22

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 85
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 96

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 48

Expert Comment

by:Dale Fye
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 22

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 85
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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

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

/gustav
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41666917
and also here
0
 
LVL 51

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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