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.

more ▼

asked Jun 09, 2011 at 03:28 AM in Default

avatar image

sagar 2
111 8 8 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Look at the styles option of CONVERT

If you are storing the date in a datetime field, then use this

 declare @MyDate datetime
 set @MyDate = '1 may 2011'
  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'
    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.

more ▼

answered Jun 09, 2011 at 03:29 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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.

Jun 09, 2011 at 04:47 AM sagar 2

@sagar: that looks like a DB2 error? What database are you running?

Jun 09, 2011 at 04:53 AM Kev Riley ♦♦

DB2 Express-C

Jun 09, 2011 at 04:59 AM sagar 2

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......

Jun 09, 2011 at 05:03 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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:

more ▼

answered Jun 09, 2011 at 03:32 AM

avatar image

871 1 3 6

(comments are locked)
10|1200 characters needed characters left


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:



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:

 DECLARE @dt datetime
 SET @dt='06-09-2011'
 SELECT DATENAME(Month,@dt) + ', ' + DATENAME(Year, @dt)

Will produce June, 2011 as a result

more ▼

answered Jun 09, 2011 at 03:44 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

Hello sir,thank you for your response. It is stored as DATE datatype.

Jun 09, 2011 at 04:14 AM sagar 2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 09, 2011 at 03:28 AM

Seen: 2351 times

Last Updated: Jun 09, 2011 at 05:03 AM

Copyright 2018 Redgate Software. Privacy Policy