?
Solved

SQL get all dates beginning with 2

Posted on 2015-02-03
6
Medium Priority
?
108 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 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 500 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 66

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 500 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:Murray Brown
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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