Solved

SQL get all dates beginning with 2

Posted on 2015-02-03
6
83 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 45

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 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

16 Experts available now in Live!

Get 1:1 Help Now