numeric to varchar to datetime

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 2008 Forums

Transact-SQL (2008)

numeric to varchar to datetime

Author

Topic

sbabu.ws
Starting Member

3 Posts

Posted - 2012-07-13 : 14:18:17

Hi forum

i have a date split into 3 columns with numeric data type
yy mm dd
1 10 22

i need to concatenate and finally load the date into datetime2 format

I'm trying
select
,cast(CONVERT(varchar(3),b.EFFYR,101)+CONVERT(varchar(3),b.EFFMO,101)+CONVERT(varchar(3),b.EFFDAY,101) as varchar(10))
from tbale_name

i get the result as 11022, i need to convert to datetime.

bABU

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 14:23:27

What does yy = 1 represent? 2001?

sbabu.ws
Starting Member

3 Posts

Posted - 2012-07-13 : 14:23:56

quote:
Originally posted by sunitabeck

What does yy = 1 represent? 2001?

yes 2001

bABU

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 14:47:50

How is 1999 represented? and 2011? This assumes that 1999 is -1 and 2011 is 11.

DECLARE @y INT, @m INT, @d INT;
SET @y = 1; SET @m = 11; SET @d = 29;
SELECT
	DATEADD(dd,@d-1,DATEADD(mm,@m-1,DATEADD(YEAR,@y,'20000101')));

sbabu.ws
Starting Member

3 Posts

Posted - 2012-07-13 : 15:09:48

quote:
Originally posted by sunitabeck

How is 1999 represented? and 2011? This assumes that 1999 is -1 and 2011 is 11.
DECLARE @y INT, @m INT, @d INT;
SET @y = 1; SET @m = 11; SET @d = 29;
SELECT
DATEADD(dd,@d-1,DATEADD(mm,@m-1,DATEADD(YEAR,@y,'20000101')));

I tried

,convert(datetime2,
(RIGHT('00'+cast(b.EFFYR as varchar(2)),2))
+(RIGHT('00'+cast(b.EFFMO as varchar(2)),2))
+(RIGHT('00'+cast(b.EFFDAY as varchar(2)),2)) ) as test

bABU

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-13 : 15:12:58

[code]DECLARE @MyCrazyDateTable TABLE (yy SMALLINT, mm TINYINT, dd TINYINT)
INSERT @MyCrazyDateTable
VALUES (1, 10, 22)

SELECT DATEFROMPARTS(2000 + yy, mm, dd)
FROM @MyCrazyDateTable[/code]EDIT: Opps. I did a SQL 2012 solution. I thought was in a different forum.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-16 : 06:03:39

Another technique

DECLARE @y INT, @m INT, @d INT;
SET @y = 1; SET @m = 11; SET @d = 29;
select cast(ltrim(@y+2000)+ltrim(@m)+ltrim(@d) as datetime)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources