Solved

SQL Date Query

Posted on 2016-11-03
9
58 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
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 23

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
 
LVL 76

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 76

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 23

Expert Comment

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

Regards,
Pawan
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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

10 Experts available now in Live!

Get 1:1 Help Now