Comparing Dates in a specific format in SQL Server
I have a column called "Mon-YY" (varchar(n) data type) in one of my tables which has values like:
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Jan-13
Feb-13
Mar-13
Apr-13
May-13
Jun-13
I want to compare these values and retrieve the records from the table based on the date range specified.
I am assuming I'd need to convert the above values into DATE format. But I'm not sure then how do I keep track of days too.
You can use the DATEPART function, but you need to do it on a full date:
SELECT DATEPART(MM, 'Aug-12') --> error
SELECT DATEPART(MM', 01-Aug-12') --> 8
So try something like this:
SELECT
DATEPART(MM, CONCAT('01-', [Mon-YY])) AS TheMonth,
DATEPART(YYYY, CONCAT('01-', [Mon-YY])) AS TheYear
FROM myTable
ORDER BY TheYear, TheMonth
SELECT CONVERT (date, REPLACE (MyColumn, '-', ' 01 '))
FROM MyTable
Something like this should give you a date. It will always be the first of the month. You can use the expression in a WHERE clause with <, >, or BETWEEN. I don't know why you would need to keep track of them.
我用了演员
-- example table and data
DECLARE @table table
(
[Mon-YY] varchar(20)
)
insert into @table values ('Aug-12')
insert into @table values ('Sep-12')
insert into @table values ('Oct-12')
insert into @table values ('Nov-12')
insert into @table values ('Dec-12')
insert into @table values ('Jan-13')
insert into @table values ('Feb-13')
insert into @table values ('Mar-13')
insert into @table values ('Apr-13')
insert into @table values ('May-13')
insert into @table values ('Jun-13')
select cast('01-' + [Mon-YY] as Date)
from @table