Truncate milliseconds - 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

Truncate milliseconds

Author

Topic

devisetti
Starting Member

30 Posts

Posted - 2008-05-08 : 05:23:18

I have got data like this below in effective date column
2008-08-05 19:18:13.000
2008-08-05 19:17:10.000

Expected output:
2008-08-05 19:17:10

I need to truncate the milliseconds and insert the same into datetime column.

I tried as below
select convert(datetime,substring(convert(varchar,getdate(),20),1,20))

Thanks in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 05:29:52

select substring(convert(varchar,getdate(),20),1,20)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:33:01

Why substring?

select convert(varchar(19), getdate(), 120)

E 12°55'05.25"
N 56°04'39.16"

devisetti
Starting Member

30 Posts

Posted - 2008-05-08 : 05:33:30

I need to insert the same into target table which is datatype as datetime column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:36:48

No worries.
SQL Server does the transformation for you.

Did you try? Have you tried?

INSERT TargetTable (MyDateTimeColumnOfDateTimeDataType)
SELECT convert(varchar(19), getdate(), 120)
from SourceTable

E 12°55'05.25"
N 56°04'39.16"

devisetti
Starting Member

30 Posts

Posted - 2008-05-08 : 05:40:36

I have tried but it inserts with milliseconds.
2008-08-05 19:38:52.000

I need to avoid last 3 zero's

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:47:53

Yes. A datetime column ALWAYS contains milliseconds.
Your original post indicated you only wanted to truncate the millisecond part.
Your only other option is to use SMALLDATETIME. But then you are restricted to hours and minutes only.

You can use a CHAR(19) column to store the data but I advice against this.
It makes things more harder to compare, sort and so other date things.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:49:35

You should not be concerned about the milliseconds.
Let the front-end application worry about that and let the front-end application take care of the formatting and let the front-end application display the value to the user.

E 12°55'05.25"
N 56°04'39.16"

devisetti
Starting Member

30 Posts

Posted - 2008-05-08 : 05:52:20

Thanks peso..

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 06:06:59

quote:
Originally posted by Peso

Why substring?

select convert(varchar(19), getdate(), 120)

E 12°55'05.25"
N 56°04'39.16"

Was just changing what the OP already had.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources