Convert text to datepart - SQL Server Forums

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)

Convert text to datepart

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-05 : 01:11:25

Hi
How can I convert or cast test to datepart.

In a field called month I have the text values '01' or '02' etc up to '12'

In a field called year I have the values '2009' or '2010' etc

I want to turn them into Month and Year respectively.

Not sure how to do this

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-05 : 02:17:44

Expected output?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-05 : 02:19:52

Do you need this?

Select cast(month+'/01/'+year as datetime)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-05 : 02:37:57

I got it working by doing this

datepart(month,(cast('2018-'+@month+'-20' as datetime)))

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-05 : 02:53:52

quote:
Originally posted by icw

I got it working by doing this

datepart(month,(cast('2018-'+@month+'-20' as datetime)))

Hi icw,

In your original post you said that you have a field which contain month value as 01 to 12.

In the solution posted by you, you will still get the same number.
I want to understand that why you are converting it to dateformat to getback the same result.

If you want the interger part (like 5 from '05') then you can convert it to int.

Example:
declare @s varchar(50)
set @s = '05'

select cast(@s as tinyint)

Regards,
Bohra

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-05 : 03:25:40

I Had to convert it in order to compare it to a another field which is a real datetime field (see below)
So I created a parameter and set the parameter to be the text value from the text field

Month (cast(faultstart as datetime)) = datepart(month,(cast('2018-'+@month+'-20' as datetime)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources