[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

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

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
Lee W, MVP
Asked:
Lee W, MVP
  • 3
  • 2
  • 2
  • +2
1 Solution
 
mcsweenSr. Network AdministratorCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dale FyeCommented:
Actually, I would suspect that you could simply use dateValue and TimeValue functions and sum them together:

DateValue([DateField]) + TimeValue([TimeField])
0
 
mcsweenSr. Network AdministratorCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
The answer is here: https:#a40358091

/gustav
0
 
Dale FyeCommented:
and also here
0
 
Gustav BrockCIOCommented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now