date/time concatenate - 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

General SQL Server Forums

New to SQL Server Programming

date/time concatenate

Author

Topic

ktkn
Starting Member

13 Posts

Posted - 2012-12-06 : 17:28:23

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

25 Posts

Posted - 2012-12-06 : 17:39:34

You need to cast them both as datetime. Set <destination_field> = cast(date_field as datetime) + cast(time_field as datetime)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-06 : 18:00:59

Is the data type of 165628? Is it a string? If so is it always 6 characters long? Or is it something else?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-06 : 18:08:25

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

ktkn
Starting Member

13 Posts

Posted - 2012-12-07 : 09:05:06

Lamprey,

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

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 09:10:23

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/

ktkn
Starting Member

13 Posts

Posted - 2012-12-07 : 09:17:37

i just shows: 165628

ktkn
Starting Member

13 Posts

Posted - 2012-12-07 : 09:31:28

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 09:36:48

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/

ktkn
Starting Member

13 Posts

Posted - 2012-12-07 : 09:40:26

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 10:17:50

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

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-07 : 10:19:18

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

ktkn
Starting Member

13 Posts

Posted - 2012-12-07 : 11:06:50

it worked, thank you

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-07 : 11:24:28

quote:
Originally posted by ktkn

it worked, thank you

Which one worked?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources