Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Date Query

Posted on 2016-11-03
Medium Priority
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'
Question by:isames
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
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41872534


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 ..
LVL 52

Expert Comment

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.
LVL 66

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.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 2000 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%'
Select revenue  From Invoices  Where InvoiceDate >= '201601' and InvoiceDate < '2017'
LVL 49

Expert Comment

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
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'.
LVL 49

Expert Comment

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....
LVL 30

Expert Comment

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


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

704 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