How to combine Date and Time before import data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Database Design and Application Architecture

How to combine Date and Time before import data?

Author

Topic

hkben
Starting Member

4 Posts

Posted - 2007-08-20 : 02:15:49

I have extracted the data from Point of Sales. it has two columns one for date and one for time in the database.
I need to combine both column into single column with the format "mm/dd/yyyy hh:mm:ss" before i import the data into the SQL server for my BI project.

Example: Data extract from Point of Sales
FDATE FTIME
20060114 063616
20060115 070743
20060116 071020

How can i combine those two when i import into SQL server like below:
FDATE
01/14/2006 06:36:16
01/15/2006 07:07:43
01/16/2006 07:10:20

million thanks.

hkben
Starting Member

4 Posts

Posted - 2007-08-20 : 04:15:22

By using the below function, i got what i wanted.
But....this is what i am doing now....

1.Export the data from POS
2.Import into SQL server (Create Table, Insert Into ..etc..)

INSERT INTO payment values ('20070701', '064640');
INSERT INTO payment values ('20070701', '065700');

3.Update & combine Date and Time together.
(update about 50,000+ records all the time, when i import new data.)

How can i modifty the exported data before import into the payment table. So that SQL server knows is datetime format??

CREATE FUNCTION fnCombineDateTime (@Date char(8), @Time char(6))
RETURNS datetime
AS
BEGIN
DECLARE @DateTime datetime
SELECT @DateTime=CONVERT(datetime, @Date + ' ' + STUFF(STUFF(@Time, 3, 0, ':'), 6, 0, ':'))
RETURN @DateTime
END

"Update Payment Table Column FDate+FTime with function fnCombineDateTime"

update payment
Set FDate = (select dbo.fnCombineDateTime(FDate, FTime))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 04:20:05

You can do it without calling a function also

update payment
Set FDatetime =CONVERT(datetime, FDate + ' ' + STUFF(STUFF(FTime, 3, 0, ':'), 6, 0, ':'))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources