Solved

SQL Date Query

Posted on 2016-11-03
9
54 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 18

Expert Comment

by:Pawan Kumar Khowal
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 18

Expert Comment

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

Regards,
Pawan
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

22 Experts available now in Live!

Get 1:1 Help Now