Character string to datetime format

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)

Character string to datetime format

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-22 : 07:38:21

Hi i've got in a txt file a date format of 19.12.2006 and time format of 09:12:57

Date Time
19.12.2006|09:12:57

I need to insert this into a datetime field into a table..

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 07:42:09

SELECT CAST(SUBSTRING(Col1, 7, 4) + SUBSTRING(Col1, 4, 2) + SUBSTRING(Col1, 1, 2) + ' ' + Col2 AS DATETIME)
FROM <YourTableNameHere>

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-22 : 07:42:27

What tool you are using to import the text file?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 07:43:20

SET DATEFORMAT DMY

SELECT CAST(Col1 + ' ' + Col2 AS DATETIME)
FROM <YourTableNameHere>

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-22 : 07:47:55

Its using a DTS tool hope this is the correct termonology from a .txt file to a SQL database table.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-22 : 08:07:32

Thanks guys that did the trick...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 08:45:22

quote:
Originally posted by Peso

SET DATEFORMAT DMY

SELECT CAST(Col1 + ' ' + Col2 AS DATETIME)
FROM <YourTableNameHere>

SET DATEFORMAT MDY
Peter Larsson
Helsingborg, Sweden

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources