Date Functions

Question

Date Functions

Basit Ali Khan

SSC Veteran

Points: 232
More actions
April 21, 2005 at 3:05 am

#163371

Hello Friend!
I have a problem. I have a table in which a cloumn named 'date' the data in this column in the format is 'mm/dd/yy' i.e. '7/1/05' but i want to write is as "July, 2005" i applied the query but it fails and give error.
SELECT (DATENAME(MONTH, DATE)+', '+DATEPART(YY,DATE)) AS 'MONTH' FROM TABLE
WHAT IS THE PROBLEM WITH THE ABOVE STATEMENT AND HOW I GET THE desired RESULT.
THANKS IN ADVANCE.
(BASIT)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

There are 2 parts to this.

DATEPART returns an integer and therefore you must CAST or CONVERT the returned value if you want to concatenate the string.

SELECT DATENAME(MONTH, DATE)+', '+ CAST(DATEPART(YY,DATE) AS CHAR(4)) AS 'MONTH' FROM TABLE

The 2nd part is whether or not the datatype of your Date column is SMALLDATETIME or DATETIME. If it is neither of these then that may be the problem.

If you have any control over the structure of your tables I would try and avoid calling columns "Date". Try to call them something more descriptive in order to avoid clashes with reserved words.

LinkedIn Profile

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Why not just use

SELECT (DATENAME(MONTH, DATE)+', '+ DATENAME(YEAR, DATE)) AS 'MONTH' FROM TABLE

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Basit Ali Khan SSC Veteran Points: 232 More actions · Answer 3

Thanks for reply. The datatype of date column is datetime and you right that i must not give column name as date becuase date is a reserve word. please tell me how to rename the column name date with the n ew name i.e. Rptdate and also told me if i want to write the first 3 letters of month i.e.(JAN, FEB, MAR, APR.....) then how can it be obtained.

thanks

(BASIT)

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Try this...

SELECT CAST((DATENAME(MONTH, DATE))AS CHAR(3)) +', '+ DATENAME(YEAR, DATE) AS 'MONTH' FROM TABLE

You can rename a column by editing in enterprise manager (quickest way)...however, you have to make sure you change the column name in every single object that references this column (all procedures, triggers, codes etc...)

**ASCII stupid question, get a stupid ANSI !!!**

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Get used to use an indepedent and safe date format. See if these help:

http://www.sql-server-performance.com/fk_datetime.asp

http://www.karaszi.com/sqlserver/info_datetime.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

btw, here's a nice trick, to get the month name from DATEPART

DECLARE @input INT

SET @input = MONTH(GETDATE())

SELECT

DATENAME(MONTH,@input*28) AS CurrentMonth

CurrentMonth

------------------------------

April

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Frank - I plead to being singularly obtuse on this one...can you please explain the *28 ??

**ASCII stupid question, get a stupid ANSI !!!**

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Why does this work? First, we're taking advantage of the fact, that, with respect to our question, both 19000421 and 20050421 are equal. Both will return "April" as month name and that's all we care about here. Next, the multiplication * 28 is used to ensure that we will always hit a day in the correct month of @input. Try it out!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]