How can i do to dont truncate Smalldatetime? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

How can i do to dont truncate Smalldatetime?

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2006-10-24 : 08:03:53

hi

this select get a full date

select CAST (getdate() AS datetime), GETDATE()

2006-10-24 09:06:23.293 2006-10-24 09:06:23.293

but

select CAST (getdate() AS smalldatetime), GETDATE()

2006-10-24 09:07:00 2006-10-24 09:06:23.293

why not 2006-10-24 09:06:23 ?

in other words the CAST is rounding

How can i do to Truncate?

Tks

Clages

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-24 : 08:16:29

select convert(datetime, convert(varchar(24), GETDATE(), 120))

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-24 : 08:32:05

from BOL on smalldatetime

quote:
smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

--returns time as 12:35
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
GO
--returns time as 12:36
SELECT CAST('2000-05-08 12:35:29.999' AS smalldatetime)

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-24 : 08:34:34

What do you want to achieve ?
remove the milliseconds from datetime ?

KH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-24 : 09:10:54

Using the F_START_OF_MINUTE function on this link will do it.

Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755


select
	Truncate_Date = dbo.F_START_OF_MINUTE( DT ),
	DT
from
	(Select DT = convert(datetime,'2006-10-24 09:06:18.680') ) a

Results:


Truncate_Date             DT
------------------------- -------------------------
2006-10-24 09:06:00.000   2006-10-24 09:06:18.680

(1 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 10:08:28

If you use front end application like vb, try using format function

Format(datecol,"MM/DD/YYYY HH:MM")

Madhivanan

Failing to plan is Planning to fail

CLages
Posting Yak Master

116 Posts

Posted - 2006-10-24 : 14:56:20

quote:
Originally posted by khtan

What do you want to achieve ?
remove the milliseconds from datetime ?

KH

Yes, but to Smalldatetime, becase if i use CAST or Convert i get a rounded date.

Clages

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-24 : 17:07:07

quote:
Originally posted by CLages

quote:
Originally posted by khtan

What do you want to achieve ?
remove the milliseconds from datetime ?

KH

Yes, but to Smalldatetime, becase if i use CAST or Convert i get a rounded date.

Clages

Datatype smalldatetime is only accurate to minutes, not seconds.

My prior post shows how to remove the milliseconds and seconds.

CODO ERGO SUM

CLages
Posting Yak Master

116 Posts

Posted - 2006-10-25 : 17:59:37

ok , but only to DateTime, I need Smalldatetime in database.
if i change the function or use Convert, I get the date
Rounded again.

As i told, i could change the column to datetime, but i can not do it now.

tks
Clages

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-25 : 19:39:30

quote:
Originally posted by CLages
ok , but only to DateTime, I need Smalldatetime in database.
if i change the function or use Convert, I get the date
Rounded again...

You are wrong about this.

You would know that if you had even tried the code I posted.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-25 : 21:18:18

"As i told, i could change the column to datetime, but i can not do it now."
Then you have to leave with accuracy in minutes

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources