Solved

SQL get all dates beginning with 2

Posted on 2015-02-03
6
88 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 47

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 31
Return 0 on SQL count 24 30
Update a text value in another table 10 40
T-SQL Default value in Select? 5 27
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

777 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