• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Formatting data into date / query

I have a text field that contains data in yyyymmddhhmmss format.

I need a query to return me anything from yesterday


20120312000000  =- sample data
20120312000014
20120312000002
0
hwassinger
Asked:
hwassinger
  • 2
1 Solution
 
Nick67Commented:
Your criteria could be WHERE Left(TheTable.TheField,8)  = "20120412"

So, look at the 8 leftmost characters, and then make a match.
0
 
Nick67Commented:
If you needed it to be dynamic, as in always yesterday from anytime onward
WHERE Left(TheTable.TheField,8)  =format(DateSerial(Year(Date()), Month(Date()) , Day(Date())-1),"yyyymmdd")
should do it
0
 
harfangCommented:
If today is 2012-04-16, yesterday is 2012-04-15, or Date()-1. For a text field, and in order to avoid calculations on the field content, try:

WHERE YourField Between Format(Date()-1, 'yyyymmdd') And Format(Date(), 'yyyymmdd')

This will evaluate to

WHERE YourField Between '20120415' And '20120416'

If you have an index on YourField, it will be used in the query's execution plan. This is the only difference with Nick67's solution.

Cheers!
(°v°)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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