Merging two fields to create a timedate field

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)

Merging two fields to create a timedate field

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-03-03 : 09:54:37

Hi everyone -

I have two fields that i need to merge into a datetime field.

One of the fields is a datetime, the other is a varchar

What would the syntax be to merge two varchar local variables
and convert them into a timestamp field?

thanks
tony

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 09:58:18

What is the format of the varchar and how do you want to merge them?

For the other question what is the format of the two varchars and how do you want to merge them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-03-03 : 10:15:19

Hello nr and everyone -

Using this query...
select convert(varchar(10), gps_time_date, 101) as a, tof as b from unit_history_table

the data i receive is formatted like this...
a b
--------------------------
03/02/2004 9:55:12 AM
03/02/2004 9:55:18 AM
03/02/2004 6:57:01 AM
03/02/2004 6:57:12 AM

I was thinking of a trigger that runs on the insert statement,
that will take these two fields, and concatenate them into a datetime field in the same table.

thanks
tony

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-03 : 10:52:46

With these formats you could try something like

CAST(convert(varchar(10), gps_time_date, 101) + ' ' + tof AS DATETIME)

Raymond

pithhelmet
Posting Yak Master

183 Posts

Posted - 2004-03-03 : 11:02:52

Thats a BIG 10-4 sir!

worked like a champ!

thanks a million

take care
tony

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-03 : 11:13:04

I am guessing that gps_time_date is your datetime field.
In that case
select convert(varchar(8), gps_time_date, 112) + ' ' + tof
from unit_history_table

would be better as it will be independent of your setup and you won't get into problems if that changes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources