Combining concatenating date and time into a datetime

Question

Using SQL Server 2008  this query works great   select CAST CollectionDate as DATE   CAST CollectionTime as TIME  from field   Gives me two columns like this   2013-01-25  18 53 00 0000000 2013-01-25  18 53 00 0000000 2013-01-25  18 53 00 0000000 2013-01-25  18 53 00 0000000                     I m trying to combine them into a single datetime using the plus sign  like this   select CAST CollectionDate as DATE    CAST CollectionTime as TIME  from field   I ve looked on about ten web sites  including answers on this site  like this one   and they all seem to agree that the plus sign should work but I get the error      Msg 8117  Level 16  State 1  Line 1   Operand data type date is invalid for add operator    All fields are non-zero and non-null  I ve also tried the CONVERT function and tried to cast these results as varchars  same problem  This can t be as hard as I m making it   Can somebody tell me why this doesn t work  Thanks for any help

User · Answer

This works in SQL 2008 and 2012 to produce datetime2   declare  date date   current timestamp  declare  time time   current timestamp   select   date as date   time as time  cast  date as datetime    cast  time as datetime  as datetime  cast  time as datetime2  as timeAsDateTime2  dateadd dayofyear datepart dayofyear  date  - 1 dateadd year datepart year  date  - 1900 cast  time as datetime2    as datetime2

User · Answer

drop table test  create table test      CollectionDate date NULL      CollectionTime   time  0  NULL      CollectionDateTime as  isnull convert datetime CollectionDate  convert datetime CollectionTime  CollectionDate   -- if CollectionDate is datetime no need to convert it above    insert test  CollectionDate  CollectionTime  values   2013-12-10    22 51 19 227             2013-12-10   null           null   22 51 19 227    select   from test  CollectionDate  CollectionTime  CollectionDateTime 2013-12-10      22 51 19        2013-12-10 22 51 19 000 2013-12-10      NULL            2013-12-10 00 00 00 000 NULL            22 51 19        NULL

User · Answer

I am using SQL Server 2016 and both myDate and myTime fields are strings  The below tsql statement worked in concatenating them into datetime  select cast  myDate         myTime  as datetime  from myTable

User · Answer

Cast it to datetime instead   select CAST CollectionDate as DATETIME    CAST CollectionTime as TIME  from field   This works on SQL Server 2008 R2   If for some reason you wanted to make sure the first part doesn t have a time component  first cast the field to date  then back to datetime

User · Answer

Assuming the underlying data types are date time datetime types   SELECT CONVERT DATETIME  CONVERT CHAR 8   CollectionDate  112             CONVERT CHAR 8   CollectionTime  108     FROM dbo whatever    This will convert CollectionDate and CollectionTime to char sequences  combine them  and then convert them to a datetime   The parameters to CONVERT are data type  expression and the optional style  see syntax documentation    The date and time style value 112 converts to an ISO yyyymmdd format  The style value 108 converts to hh mi ss format  Evidently both are 8 characters long which is why the data type is CHAR 8  for both   The resulting combined char sequence is in format yyyymmdd hh mi ss and then converted to a datetime

User · Answer

Solution  1   datetime arithmetic Given  myDate  which can be anything that can be cast as a DATE  and  myTime  which can be anything that can be cast as a TIME  starting SQL Server 2014  this works fine and does not involve string manipulation  CAST CAST  myDate as DATE  AS DATETIME    CAST CAST  myTime as TIME  as DATETIME   You can verify with  SELECT  GETDATE             CAST CAST GETDATE   as DATE  AS DATETIME    CAST CAST GETDATE   as TIME  as DATETIME   Solution  2   string manipulation SELECT  GETDATE             CONVERT DATETIME  CONVERT CHAR 8   GETDATE    112          CONVERT CHAR 8   GETDATE    108    However  solution  1  is not only 2-3x faster than solution  2   it also preserves the microsecond part  See SQL Fiddle for the solution  1  using date arithmetic vs solution  2  involving string manipulation

User · Answer

An easier solution  tested on SQL Server 2014 SP1 CU6     Code       DECLARE  Date date   SYSDATETIME     DECLARE  Time time 0    SYSDATETIME     SELECT CAST CONCAT  Date        Time  AS datetime2 0      This would also work given a table with a specific date and a specific time field  I use this method frequently given that we have vendor data that uses date and time in two separate fields

User · Answer

The simple solution  SELECT CAST CollectionDate as DATETIME    CAST CollectionTime as DATETIME  FROM field

User · Answer

DECLARE  ADate Date   ATime Time   ADateTime Datetime  SELECT  ADate    2010-02-20    ATime    18 53 00 0000000   SET  ADateTime   CAST         CONVERT Varchar 10    ADate  112                 CONVERT Varchar 8    ATime  AS DateTime   SELECT  ADateTime  A nice datetime       This will render you a valid result

User · Answer

Concat date of one column with a time of another column in MySQL   SELECT CONVERT concat CONVERT  dateColumn  DATE      CONVERT  timeColumn   TIME    DATETIME  AS  formattedDate  FROM dbs tableName

User · Answer

dealing with dates  dateadd must be used for precision  declare  a DATE   getdate   declare  b time 7    getdate   select  b   A  GETDATE    DATEADD day  DATEDIFF day  0   a   cast  b as datetime2 0

User · Answer

SELECT CONVERT DATETIME  CONVERT CHAR 8   date  112          CONVERT CHAR 8   time  108     FROM tablename

[sql] Combining (concatenating) date and time into a datetime

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to datetime