Solved

SQL Date Query

Posted on 2016-11-03
9
68 Views
Last Modified: 2016-11-22
I have a column that is data type varchar(6).

The column contains data like YYYYMM. Example 201610 for October2016.

I would like to be able to handle the data in this column like if it was a date without the days, so I could do a query like:

Select revenue
From Invoices
Where InvoiceDate > '201601'
0
Comment
Question by:isames
[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
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41872534
Try....

1...

DECLARE @dt AS DATETIME = '2016/01/01'
Select revenue
From Invoices
Where CAST( LEFT(InvoiceDate ,4)  + '/' + RIGHT(InvoiceDate ,2) + '/' + '01' AS DATETIME ) > @dt

or Try 2 ..

DECLARE @Date AS VARCHAR(6) = '201610'
SELECT CAST( LEFT(@Date,4)  + '/' + RIGHT(@Date,2) + '/' + '01' AS DATETIME )

Select revenue
From Invoices
Where InvoiceDate > CAST( LEFT(@Date,4)  + '/' + RIGHT(@Date,2) + '/' + '01' AS DATETIME )

Hope it helps ..
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41872578
@isames - What happened when you tried it? I think it should work as expected, as long as the month number is always zero padded.  If not, try casting the underlying column to a date, using a static '01' for the day value:

    WHERE CONVERT(date, InvoiceDate, '01', 112) > CONVERT(date, '20160101', 112)

However, that will prevent the db from using any indexes on that column. So if possible, it would be better to convert InvoiceDate to type date or date/time.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41872665
>I would like to be able to handle the data in this column like if it was a date without the days,
There is no such animal, as you'd always have to parse the value to add the first day of the month, then convert to a date.   And you'd have to do that in every query.

A vastly better idea would be to create your own calendar table where the YYYYMM and the date are columns.  Check out SQL Server Calendar Table which has code ready-to-execute to pull that off.

That way, it's a simple JOIN on your calendar table, YYYYMM value, then grab the date.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points (awarded by participants)
ID: 41872886
>>so I could do a query like:

Why do you think you need to convert it to a date first?

That seems like a perfectly valid query that will pull in everything from January 2016 to present.

Say today is February 4, 2017 and you want all of 2016:

Select revenue  From Invoices  Where InvoiceDate like '2016%'
or
Select revenue  From Invoices  Where InvoiceDate >= '201601' and InvoiceDate < '2017'
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41873131
Select revenue  From Invoices  
Where InvoiceDate > '201601' and InvoiceDate < '201700'

you would need '00' or '01' in that I believe

Think of it as being an integer

     201602 is not less than 2017

    201602 is less than 201700
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41873149
>>Think of it as being an integer

No need.  In the original post:  "I have a column that is data type varchar(6)."

It is a string compare.  ASCII sorting doesn't need the '00'.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41873166
:( sorry yes you are correct

I was going to mention making it an integer as an alternative but had to do  stand-up....
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873259
Hi isames,
Did you tried out the suggestions ?

Regards,
Pawan
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change this SQL to get all nodes 3 55
Linked Server - SP with Param to VIew 7 29
Database Owner 3 45
SQL Server remove line breaks and tabbed 2 26
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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