asked
Date function in SQL
Hello, I am using a query to retrieve the date of a particular event. The date is stored as mm-dd-yyyy. Is there any method where i can retrieve the date as month_name,year? For example for the date 05-01-2011,the query should return May,2011.
Look at the *styles* option of [CONVERT][1] If you are storing the date in a datetime field, then use this declare @MyDate datetime set @MyDate = '1 may 2011' select @MyDate, substring(CONVERT(varchar, @MyDate, 106), 4, LEN(CONVERT(varchar, @MyDate, 106))) Otherwise if it is a string, convert it to a date first... declare @MyDate2 varchar(10) set @MyDate2 = '05-01-2011' select @MyDate2, CONVERT(datetime, @MyDate2, 110), substring(CONVERT(varchar, CONVERT(datetime, @MyDate2, 110), 106), 4, LEN(CONVERT(varchar, CONVERT(datetime, @MyDate2, 110), 106))), DATENAME(m, @MyDate2) + ' ' + DATENAME(yy, @MyDate2) It goes without saying, though, that SQL isn't the best place to be formatting your output, and it should be handled by the presentation layer. [1]:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Ahh - ignore my answer then, I presumed it was a SQL Server question. I'll retag your question, but I wouldn't be too sure of an answer here......
I tried using select substring(CONVERT(varchar, date, 106), 4, LEN(CONVERT(varchar, date, 106))) from table_name Error : "VARCHAR" is not valid in the context where it is used. and i tried using select substring(CONVERT(varchar(20), date, 106), 4, LEN(CONVERT(varchar(20), date, 106))) from table_name Error : SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884 The datatype of my date is DATE.
@sagar: that looks like a DB2 error? What database are you running?
There'll be a few ways to do it. CONVERT allows you to format dates into various formats, but there isn't a style that's exactly what you want. So one way is: SELECT DATENAME(month, GETDATE()) + ',' + CAST(YEAR(GETDATE()) AS CHAR(4))
**EDIT** Since you're using DB2, here's what I **think** will work, with the reservation that I'm not a DB2 developer. - Instead of DATENAME(Month,@dt) you can use MONTHNAME(@dt) - Instead of DATANEM(Year,@dt) you can use CHAR(YEAR(@dt)) If you have your dates in column dt of table T1 in schema S1: SELECT MONTHNAME(dt) + ', ' + CHAR(YEAR(dt)) FROM S1.T1 **END EDIT** When you say the date is stored as mm-dd-yyyy, do you mean that it's stored as a varchar datatype? Otherwise it's stored as a datetime or date datatype and may be presented as for example mm-dd-yyyy. Anyway, if you have it as a varchar, you can cast it to datetime, and from that get the Monthname and year: SET DATEFORMAT mdy GO DECLARE @dt datetime SET @dt='06-09-2011' SELECT DATENAME(Month,@dt) + ', ' + DATENAME(Year, @dt) Will produce **June, 2011** as a result
