SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Extracting month name from a date column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rookie_sql
Constraint Violating Yak Guru

Ireland
443 Posts

Posted - 05/15/2006 :  07:45:16  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 05/15/2006 :  07:52:11  Show Profile  Reply with Quote
update tbl_Voy_RCATransfer
set [Month] = DATENAME(month, [Date])



KH

Go to Top of Page

rookie_sql
Constraint Violating Yak Guru

Ireland
443 Posts

Posted - 05/15/2006 :  07:57:42  Show Profile  Reply with Quote
Am getting this error now .

Syntax error converting datetime from character string.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/15/2006 :  08:08:37  Show Profile  Reply with Quote
the column [Date] is a varchar ?


KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 05/15/2006 :  08:10:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is your Datecol DateTime datatype?

Dont update. Just use select

select DATENAME(month, [Datecol]) from yourTable

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 05/15/2006 08:10:51
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/15/2006 :  08:11:15  Show Profile  Reply with Quote
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))



KH

Go to Top of Page

rookie_sql
Constraint Violating Yak Guru

Ireland
443 Posts

Posted - 05/15/2006 :  08:11:44  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/15/2006 :  08:14:01  Show Profile  Reply with Quote
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



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/15/2006 :  08:24:48  Show Profile  Reply with Quote
" 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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 05/15/2006 :  08:30:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 05/15/2006 :  08:31:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
update tbl_Voy_RCATransfer
set [Month] = DATENAME(month, [Date])

Please make sure that [Month] column is at least 9 characters wide.
Go to Top of Page

rookie_sql
Constraint Violating Yak Guru

Ireland
443 Posts

Posted - 05/15/2006 :  09:06:01  Show Profile  Reply with Quote
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)

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/15/2006 :  09:14:40  Show Profile  Reply with Quote
"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.


KH

Go to Top of Page

rookie_sql
Constraint Violating Yak Guru

Ireland
443 Posts

Posted - 05/15/2006 :  09:15:34  Show Profile  Reply with Quote
Thanks for all your help on this matter.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000