convert Time

[jerry]

Hi everyone,
How can I convert 18:00:00 to 18:00

 

[Alex White MCDBA MCSE]

Hi,

=format(now,"short time")

=format(now,"hh:mm")

hope its what you are looking for

 

[ÀÒÁ]

Hi everyone,
How can I convert 18:00:00 to 18:00

 

[Bill Patten]

er, remove the last 2 zero's?

You might be talking about changing the column from datetime to
smalldatetime. If so first you have to convert all the data to smalldatetime
then you can change the column using the ADP, Enterprise manager or T-sql
alter column. To convert the data I used this Stored Proc (from my notes.)

UPDATE dbo.tblWorkOrder
SET WOTime = CONVERT(smalldatetime, CONVERT(varchar(9), WOTime,
108))
108 tells the convert function that you want the time. Then you can use
either ADP or enterpirse manager to change the field (column) or t-sql alter
column

Bill

 

[Andrew Backer]

Small datetimes just keep track of the standard time with less
precision.

From BOL :

"The smalldatetime data type stores dates and times of day with less
precision than datetime. SQL Server stores smalldatetime values as two
2-byte integers. The first 2 bytes store the number of days after
January 1, 1900. The other 2 bytes store the number of minutes since
midnight. Dates range from January 1, 1900, through June 6, 2079, with
accuracy to the minute"

It's not the datatype, its the outputter. Date is date, regardless of
precision. The first post about what to do inside VBA to do it is
correct, and you can also use the 'format' property if you just want to
display it witout it.

If you want to do stuff with this output inside sql server, look at the
convert() function, which also takes styles. It's opaque, but it
works. I think 120 is the style for 2005-06-06, etc. Also, the
cast(datefield as nvarchar(length) ) is a nice hack too, since it just
converts to a string, and chops off everything after length, which
gives you a convenient way to lop of all the time, or just part of it.

HTH,
Andrew Backer