Solved

SQL get all dates beginning with 2

Posted on 2015-02-03
6
86 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 46

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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:ScottPletcher
ScottPletcher 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

12 Experts available now in Live!

Get 1:1 Help Now