Solved

SQL get all dates beginning with 2

Posted on 2015-02-03
6
92 Views
Last Modified: 2015-02-06
Hi

I use the following SQL statement to get all decimal values beginning with 2

Select * From MARA_MBEW Where SUBSTRING(CAST([Total Stock] AS VARCHAR),1, 1) = 2

I try to do the same thing with a date column using the following statement but it doesn't seem to work

Select * From MARA_MBEW Where SUBSTRING(CAST([MyDate] AS VARCHAR),1, 1) = 2
0
Comment
Question by:murbro
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 40586309
Does 'by beginning with 2' mean after 1999-12-31 and before 3000-01-01?

Select * From MARA_MBEW Where MyDate > '19991231' AND MyDate < '30000101'
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 125 total points
ID: 40586314
If [MyDate] is a date column (i.e. in datetime or something similar), then:

If you are looking for a Day starting with "2" then use:

Select * From MARA_MBEW Where Day([MyDate]) Between 20 and 29

(assuming "2" is the start of a 2-digit number, and does not include the day "02").

You can also use Month() and Year() for similar criteria.
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40586381
Good point Jim. But if it's a datetime column then he can use the YEAR function:
Select * From MARA_MBEW Where YEAR([MyDate]) >= 2000 

Open in new window

I wouldn't mind with year 3000 and further :)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40586394
Indeed.  We can use an asker clarification on what 'date beginning with 2' means.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 40586462
You should avoid using functions on columns as that prevents index seeks (in technical terms, it's not "sargable").

Thus:

Select * From MARA_MBEW
Where [Total Stock] Like '2%'

Or, as Jim noted:
Where MyDate >= '20000101' AND MyDate < '30000101'

I personally prefer to stick to a >= and < format, because if time is included it makes it much easier, for example, on or after 8PM on the first of the month thru the end of the month:
Where MyDate >= '20150101 20:00' And MyDate < '20150201'
Trying to write that using > is very tricky, and depends on the specific data type of MyDate.
0
 

Author Closing Comment

by:murbro
ID: 40593098
Thanks a lot for the help. As it turns out the idea I had had no direction, but thanks for the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

838 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