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
 General SQL Server Forums
 New to SQL Server Programming
 date/time concatenate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ktkn
Starting Member

13 Posts

Posted - 12/06/2012 :  17:28:23  Show Profile  Reply with Quote
from exting table, i have 2 fields for date and time modified. I need to create the new table with only one field for DateTimeModified and put those two value into this new field. Can
you show me how?

tbl1
date_modified time_modified
2002-11-11 00:00:00.000 165628
2002-11-11 00:00:00.000 165638

tb2
DateTimeModified

query:
insert into tb2
set DateTimeModified =select (date_modified+time_modified) as DateTimeModified
?

Thanks

nelsont
Starting Member

USA
25 Posts

Posted - 12/06/2012 :  17:39:34  Show Profile  Reply with Quote
You need to cast them both as datetime. Set <destination_field> = cast(date_field as datetime) + cast(time_field as datetime)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 12/06/2012 :  18:00:59  Show Profile  Reply with Quote
Is the data type of 165628? Is it a string? If so is it always 6 characters long? Or is it something else?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 12/06/2012 :  18:08:25  Show Profile  Reply with Quote
Without knowing the details, Here is a guess:
SELECT 
    CAST(LEFT(date_modified, 11) + STUFF(STUFF(time_modified, 3, 0, ':'), 6, 0, ':') AS DATETIME)
FROM 
    tbl
Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 12/07/2012 :  09:05:06  Show Profile  Reply with Quote
Lamprey,

the data type of 165628 is int, then what should i do?

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/07/2012 :  09:10:23  Show Profile  Reply with Quote
quote:
Originally posted by ktkn

Lamprey,

the data type of 165628 is int, then what should i do?

Thanks


what does it represent?
does it represent time of 16:56:28 or is it 165628 seconds ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 12/07/2012 :  09:17:37  Show Profile  Reply with Quote
i just shows: 165628
Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 12/07/2012 :  09:31:28  Show Profile  Reply with Quote
i had tried Lamprey's code but got an error:
Conversion failed when converting datetime from character string..

old values
date_modified time_modified
2004-09-03 00:00:00.000 222620

new with the qurey after Concatenate
select cast(date_modifiedas datetime) + cast(time_modified
as datetime)
2614-03-10 00:00:00.000

Edited by - ktkn on 12/07/2012 09:37:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/07/2012 :  09:36:48  Show Profile  Reply with Quote
quote:
Originally posted by ktkn

i just shows: 165628


I was asking what it represents
DO you mean you dont know whether above means 16:56:28 or just an int value?

I guess it should be former though!

also how are date values present in date_modified? is it always in same format or is it of datetime type?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 12/07/2012 :  09:40:26  Show Profile  Reply with Quote
what I see is the data type for time_modified is int, it has diffrent time on it. i see some have 5 number, 6 number, etc. The data type for date_modified is datetime.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/07/2012 :  10:17:50  Show Profile  Reply with Quote

select dateadd(ss,time_modified %100,dateadd(n,(time_modified %10000)/100,dateadd(hh,time_modified/10000,date_modified ))) from table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2012 :  10:19:18  Show Profile  Reply with Quote
Working with the assumption that time_modified represents the time as an integer in format HHMMSS and the data is valid, this will work:
select
	a.*,
	time_datetime =
	stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':'),
	Combined_Datetime = date_modified+
	case
	when -- test for invalid time
	time_modified between 0 and 235959 and
	isDate(stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':')) = 1
	then
	stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':')
	end
from
	( --Test Date
	select	date_modified = convert(datetime,'20040903'),
		time_modified = 165628
	union all
	select	date_modified = convert(datetime,'20040904'),
		time_modified = 000201
	union all
	select	date_modified = convert(datetime,'20040905'),
		time_modified = 009901
	union all
	select	date_modified = convert(datetime,'20040906'),
		time_modified = 1245628
	) a
order by
	a.date_modified,
	a.time_modified


Results:
date_modified           time_modified time_datetime Combined_Datetime
----------------------- ------------- ------------- -----------------------
2004-09-03 00:00:00.000        165628 16:56:28      2004-09-03 16:56:28.000
2004-09-04 00:00:00.000           201 00:02:01      2004-09-04 00:02:01.000
2004-09-05 00:00:00.000          9901 00:99:01      NULL
2004-09-06 00:00:00.000       1245628 24:56:28      NULL

Edited by - Michael Valentine Jones on 12/07/2012 10:29:29
Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 12/07/2012 :  11:06:50  Show Profile  Reply with Quote
it worked, thank you
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/07/2012 :  11:24:28  Show Profile  Reply with Quote
quote:
Originally posted by ktkn

it worked, thank you



Which one worked?



CODO ERGO SUM
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.17 seconds. Powered By: Snitz Forums 2000