panwar.jt (12/21/2010)
I had fix the problemmm.As per my understanding we can't alter a datetime column directly into numeric type. So I convert frist into varchar(8) then to Numeric.
Thanks for your reply..................
Panwar, would you mind sharing the business need you had for this?
What you've posted doesn't add up to me as a requirement. For example:
DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()
DECLARE @varcharVar VARCHAR(8)
SET @varcharVar = CONVERT( VARCHAR(8), @dateVar)
DECLARE @numVar NUMERIC(6)
SET @numVar = @varcharVar
PRINT @dateVar
print @varcharVar
PRINT @numVar
Comes up with this:
Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to numeric.
Some manipulation to get this to behave in any method...
DECLARE @dateVar DATETIME
SET @dateVar = GETDATE()
DECLARE @varcharVar VARCHAR(8)
SET @varcharVar = CONVERT( VARCHAR(8), @dateVar, 121)
DECLARE @numVar NUMERIC(6)
SET @numVar = REPLACE( @varcharVar, '-', '')
PRINT @dateVar
print @varcharVar
PRINT @numVar
Produces:
Jan 5 2011 2:15AM
2011-01-
201101
Giving you, at best, a year/month code. If that's your requirement, there's more precise ways to arrive at the value.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA