Sql Date Format - Month and Year Only at Richard Santos

i remember there is one time where i have to edit an old module where in the date field in sql is not a date data type but instead, it is a char type… they did that because they wanted to store only the month and year of the date in sql… at first it wasn’t a big deal after all until i have to return the data with a given date range… now that’s a problem… my problem…

since the date is in char type, it won’t be easy to select the data with a given range..
and so i came up with this solution… this is how i make a date range with char data type..

say we have to select all datas with these given date range: from 04/2003 to 03/2006

my query would be:

select field1, field2, dateField from table1 where (cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003) or (cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006) or (cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4) as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006

now lets disect the code…
for the first where condition:
(cast(substring(dateField,1,2) as int) >= 4 and cast(substring(dateField,4,4) as int)=2003)
this will return all the data with the date range from 04/2003 to 12/2003…

for the second where condition:
(cast(substring(dateField,1,2) as int) <= 3 and cast(substring(dateField,4,4) as int)=2006)
this will return all the data with the date range from 01/2006 to 03/2006..

for the third and last where condition:
(cast(substring(dateField,1,2) as int) between 1 and 12 AND cast(substring(dateField,4,4) as int) > 2003 and cast(substring(dateField,4,4) as int) < 2006
this will return all the data with the date from 01/2004 to 12/2004 and from 01/2005 to 12/2005

if you don’t know how to use the substring function in sql… just google it… there are lots of threads and forums out there…

a smarter way to do this is convert the varchar type date into a real date format… in that way, you can easily make a date range because it is now in date type…
say you have “08/2006″ as august 2006, all you have to do is insert a “01/” on the left side to make it “01/08/2006″.. after that you can now convert it to date format… since you only need the month and year, the value of the day does not matter…

to insert the “01/” in the left side of your varchar date and convert it to date format:

convert(datetime, ‘01/’ + dateField,103)
*101 means the date format will be dd/mm/yy

now every time you need to the month and year.. use the code month() to return only the value of month and year() for the value of year…

sample:
say we have “30/03/1984″ in dateField

select month(dateField)
from sampleTbl

this will return “03″… and if you use the year() function.. it will return “1984″

another way is to use a contiguous string format… just convert your varchar date into this format ‘200608′ (means agust 2006).. then use <,>,= operators to create a range… just use substring() function to arrange your data into this format…

Bookmarks