isames
asked on
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'
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'
@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.
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.
>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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
>>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'.
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'.
:( sorry yes you are correct
I was going to mention making it an integer as an alternative but had to do stand-up....
I was going to mention making it an integer as an alternative but had to do stand-up....
Hi isames,
Did you tried out the suggestions ?
Regards,
Pawan
Did you tried out the suggestions ?
Regards,
Pawan
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 ..