April 23, 2019 at 8:34 am
/*Create new colunm TimeOnly */
ALTER TABLE ['Job info$']
ADD [TimeOnly] DATETIME NOT NULL
The Datetype DATETIME does always store both the date and the time. You can alter the date in this 'column' but you can not remove it. The name TimeOnly indicates that this column is only to be used for the time part. But on each usage of this column care should be taken to remove the date part (which is always there).
Below some examples of storage of date and time and time only.
--
-- Datatypes :
--
-- Datetime Always holds a date and a time. (00:00:00 through 23:59:59.997)
-- Time(0) Holds a time with second accuracy.
-- Time(7) Holds time, range 00:00:00.0000000 through 23:59:59.9999999.
--
DROP TABLE IF EXISTS Hold_Data
CREATE TABLE Hold_Data(
label varchar(60)
, Time1 time(0)
, Time7 time(7)
, Date_Plus_time datetime)
INSERT INTO Hold_Data values ('Hour_minute_seconds','12:23:46','12:23:46','12:23:46')
INSERT INTO Hold_Data values ('TIME fractional','12:23:46.123456789','12:23:46.123456789','12:23:46.789')
INSERT INTO Hold_Data values ('Date AND Time fractional','20201122 12:23:46.123456789','20201122 12:23:46.123456789','20201122 12:23:46.789')
--
-- shows the data in different formats with different techniques.
--
select * from Hold_Data
select label
,FORMAT( [Date_Plus_time] , N'hh\:mm\:ss') time_format
,CONVERT(TIME , Date_Plus_time ) time_datatype
,CONVERT(VARCHAR(30), Date_Plus_Time) convert_varchar
,CONVERT(VARCHAR(30), Date_Plus_Time,126) substring_varchar0
,SUBSTRING(CONVERT(VARCHAR(30), Date_Plus_Time,126),12,8) substring_varchar1
,SUBSTRING(CONVERT(VARCHAR(30), Date_Plus_Time,126),12,12) substring_varchar1
from Hold_Data
Explanation, a table with a number of columns is created, one label column and one column to hold both date plus time and two columns to hold the time. Filling of this table is done with 'more' information than 'fits' in the columns, for the time columns the date will not be inserted, and the accuracy is reduced to the storage format.
The first select statement shows quite fairly what the table is holding.
The second select statement shows a number of possibilities how the data can be formatted. Only the Date_Plus_Time field is shown here, because this hold the 'most' data.
Greetings,
Ben
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply