Extracting month name from a date column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Extracting month name from a date column

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-15 : 07:45:16

Hi i've a date column dd/mm/yy, i want to display the month name (May) from the given date column in a month column.

Example Date = 15/05/2006
Month = May

Am i on the right track ?

update tbl_Voy_RCATransfer
set [Month] = (SELECT DATENAME(month, [Date])
from tbl_Voy_RCATransfer)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 07:52:11

[code]update tbl_Voy_RCATransfer
set [Month] = DATENAME(month, [Date])[/code]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-15 : 07:57:42

Am getting this error now .

Syntax error converting datetime from character string.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 08:08:37

the column [Date] is a varchar ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-15 : 08:10:35

Is your Datecol DateTime datatype?

Dont update. Just use select

select DATENAME(month, [Datecol]) from yourTable

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 08:11:15

If the column [Date] is a string (char or varchar) then you have to convert to datetime first.

update tbl_Voy_RCATransfer
set [Month] = DATENAME(month, convert(datetime, [Date], 103))

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-15 : 08:11:44

Nope i check it, its a datetime. i am using a function to convert it. here is the function it may have something got to do with it, as it set them to char(2) .

CREATE FUNCTION dbo.udf_date_only(@date_in datetime)
RETURNS datetime
AS

BEGIN

RETURN CONVERT(datetime, CAST(DAY(@date_in) AS char(2)) + '/' + CAST(MONTH(@date_in) AS char(2)) + '/' + CAST(YEAR(@date_in) AS char(4)), 103)

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 08:14:01

your function can be simplified to


CREATE FUNCTION dbo.udf_date_only(@date_in datetime)
RETURNS datetime
AS
BEGIN
    RETURN dateadd(day, datediff(day, 0, @date_in), 0)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 08:24:48

" Nope i check it, its a datetime"
If the column is of datetime data type, then it should not gives you any error with DATENAME(month, [Date]) at all.

What is the data type for [Month] ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-15 : 08:30:07

Easier to use this:
CREATE FUNCTION dbo.udf_date_only(@date_in datetime)
RETURNS datetime
AS
BEGIN
RETURN CONVERT(VARCHAR, @date_in_datetime, 112)
END

But I think that column Month does not hold enough space (characters) for example 'Septemberä which is 9 characters long...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-15 : 08:31:31

update tbl_Voy_RCATransfer
set [Month] = DATENAME(month, [Date])

Please make sure that [Month] column is at least 9 characters wide.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-15 : 09:06:01

quote:
Originally posted by khtan

" Nope i check it, its a datetime"
If the column is of datetime data type, then it should not gives you any error with DATENAME(month, [Date]) at all.

What is the data type for [Month] ?

KH

The porblem was with the month i had it set to datetime, and i;'ve now changed it to varchar(10)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 09:14:40

"The porblem was with the month i had it set to datetime, and i;'ve now changed it to varchar(10) "
That sure solved the mystery.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-15 : 09:15:34

Thanks for all your help on this matter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources