Solved

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

Posted on 2014-10-02
12
146 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
The answer is here: https:#a40358091

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
and also here
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now