• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

SQL Date Query

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
isames
Asked:
isames
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
_agx_Commented:
@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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
>>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
 
PortletPaulCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
PortletPaulCommented:
:( sorry yes you are correct

I was going to mention making it an integer as an alternative but had to do  stand-up....
0
 
Pawan KumarDatabase ExpertCommented:
Hi isames,
Did you tried out the suggestions ?

Regards,
Pawan
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now