Syntax - Adding date and time together most efficiently

• DeveloperDotNetSQL

  SSC Veteran

  Points: 239

  More actions

  January 12, 2011 at 3:32 pm

  #230291

  I am looking for the simplest method of adding my date and time strings together to form a datetime.

  I can do as per below but it's the long way around..

  i.e. DD/MM/YYYY + hh:mm:ss

  @Date=13/01/2011 & @Time=09:00:00

  CONVERT(VARCHAR(4),SUBSTRING(@Date,7,4))+ '-'

  + CONVERT(VARCHAR(2),SUBSTRING(@Date,4,2)) + '-'

  + CONVERT(VARCHAR(2),SUBSTRING(@Date,1,2)) + ' '

  + CONVERT(VARCHAR(8),SUBSTRING(@Time,1,8)) + '.000'

  I am trying to get a date which I can compare to a tables datetime field in a select, as per above the format I have been converting to is 2011-01-13 00:00:00.000 but am happy to use any format that will work.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 12, 2011 at 3:36 pm

  #1272688

  Assuming your servers default date format is DDMMYYYY:

  select convert(datetime,@date)+convert(datetime,@time)

• DeveloperDotNetSQL

  SSC Veteran

  Points: 239

  More actions

  January 12, 2011 at 3:39 pm

  #1272691

  Thanks for the fast reply, our local datetime format is DD/MM/YYYY, how will it know to convert 01/02/2011 to the 1st of February and not 2nd of January?

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 12, 2011 at 3:48 pm

  #1272697

  DeveloperDotNetSQL (1/12/2011)

  ---

  Thanks for the fast reply, our local datetime format is DD/MM/YYYY, how will it know to convert 01/02/2011 to the 1st of February and not 2nd of January?

  You should read SQL Server Books Online about DATEFORMAT and CONVERT.

  If you are not sure what is will do, test it to see what happens.

• DeveloperDotNetSQL

  SSC Veteran

  Points: 239

  More actions

  January 12, 2011 at 4:17 pm

  #1272711

  OK, here's an example, this generates the following error.

  SET DATEFORMAT mdy

  DECLARE @Date varchar(50)

  DECLARE @time varchar(50)

  SET @Date = '13/01/2011'

  SET @Time = '09:00:00'

  select convert(datetime,@Date)+convert(datetime,@Time)

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 12, 2011 at 4:20 pm

  #1272715

  DeveloperDotNetSQL (1/12/2011)

  ---

  OK, here's an example, this generates the following error.

  SET DATEFORMAT mdy

  DECLARE @Date varchar(50)

  DECLARE @time varchar(50)

  SET @Date = '13/01/2011'

  SET @Time = '09:00:00'

  select convert(datetime,@Date)+convert(datetime,@Time)

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  You can either use

  SET DATEFORMAT DMY;

  or specify the conversion within the query:

  SET DATEFORMAT mdy

  DECLARE @Date varchar(50)

  DECLARE @time varchar(50)

  SET @Date = '13/01/2011'

  SET @Time = '09:00:00'

  select convert(datetime,@Date,103)+convert(datetime,@Time)

  Or you can write your dates out in ISO format which works in any region setting.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]