[dba-SQLServer]Convert int month to char month

You are right, I had forgotten to change my convert variable from varchar(2) to datetime.
	I had:	LEFT(CONVERT(varchar(2), MyDate, 107), 3))
	I needed:	LEFT(CONVERT(datetime, MyDate, 107), 3))

I had varchar(2) set up because I am concatenating the month with the rest of the date to get it in a specific format to feed another system.  Until I found out how to get the month in a text format, I was testing with just the month function, converting the return integer to a string so that it could be concatenated with the rest of the string.  So the last one listed here works.  I can get rid of my function now, although it was fun to finally get into those.  Have wanted to play in there but hadn't had a need yet.

Thanks for all your help, everyone.

Ken Stoker
Technology Commercialization
Information Systems Administrator

PH: (509) 375-3758
FAX: (509) 375-6731
E-mail:  Kenneth.Stoker at pnl.gov <mailto:Kenneth.Stoker at pnl.gov> 



-----Original Message-----
From: James Barash [mailto:jbarash at bellatlantic.net]
Sent: Friday, April 25, 2003 9:34 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Convert int month to char month


You need to pass a datetime variable to convert. Try
convert(datetime,cast('4/25/03' as datetime),107)

107 is a style that should return MMM DD YYYY HH:MM
See BOL for a list of styles.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Friday, April 25, 2003 12:17 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Convert int month to char month


Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Got an error -- what's the 107? I'm using SQL Server 2000. 

Susan H. 


> Ken,
> Try something like this:
> 
> left(convert(datetime, mydate, 107),3)
> 
> This works in SQL2000, I don't know of convert exists in SQL7.
> 
> James Barash
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of 
> Stoker, Kenneth E
> Sent: Friday, April 25, 2003 11:27 AM
> To: dba-SQLServer (E-mail)
> Subject: [dba-SQLServer]Convert int month to char month
> 
> 
> Does anyone know of a way to convert the return value of 
> month([mydate]) from an integer to its equivalent char value?
> 
> 1 JAN
> 2 FEB
> 3 MAR
> .....
> 
> Thank you for your help.
> 
> Ken Stoker
> Technology Commercialization
> Information Systems Administrator
> PH: (509) 375-3758
> FAX: (509) 375-6731
> E-mail:  Kenneth.Stoker at pnl.gov
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 
> 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com