convert datetime into date and time.

• igngua

  SSCrazy

  Points: 2510

  More actions

  July 19, 2010 at 3:11 pm

  #224990

  Hi!

  I have one column with datetime data.

  example:

  01-06-2010 00:03:00

  I need to convert that into two columns:

  date

  01-06-2010

  and

  time

  00:03:00

  thanks in advance.

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  July 19, 2010 at 3:34 pm

  #1195164

  A lot of times this sort of thing is better done on the interface side.

  However, if you need to do this via T-SQL, I would suggest you read this article[/url] by Seth Phelabaum.

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 19, 2010 at 3:36 pm

  #1195165

  select

  a.DT,

  Date_Only = dateadd(dd,datediff(dd,0,a.DT),0),

  Time_Only = a.DT-dateadd(dd,datediff(dd,0,a.DT),0)

  from

  ( -- Test Data

  select DT = convert(datetime,'1753-01-01 00:00:00.003')

  union all

  select DT = convert(datetime,'1753-01-01 07:21:09.997')

  union all

  select DT = convert(datetime,'1753-01-01 23:59:59.997')

  union all

  select DT = getdate()

  union all

  select DT = convert(datetime,'2006-04-27 07:23:11.247')

  union all

  select DT = convert(datetime,'2006-04-27 07:21:09.333')

  union all

  select DT = convert(datetime,'9999-12-31 00:00:00.003')

  union all

  select DT = convert(datetime,'9999-12-31 07:21:09.997')

  union all

  select DT = convert(datetime,'9999-12-31 23:59:59.997')

  ) a

  order by

  a.DT

  Results:

  DT Date_Only Time_Only

  ----------------------- ----------------------- -----------------------

  1753-01-01 00:00:00.003 1753-01-01 00:00:00.000 1900-01-01 00:00:00.003

  1753-01-01 07:21:09.997 1753-01-01 00:00:00.000 1900-01-01 07:21:09.997

  1753-01-01 23:59:59.997 1753-01-01 00:00:00.000 1900-01-01 23:59:59.997

  2006-04-27 07:21:09.333 2006-04-27 00:00:00.000 1900-01-01 07:21:09.333

  2006-04-27 07:23:11.247 2006-04-27 00:00:00.000 1900-01-01 07:23:11.247

  2010-07-19 17:32:04.450 2010-07-19 00:00:00.000 1900-01-01 17:32:04.450

  9999-12-31 00:00:00.003 9999-12-31 00:00:00.000 1900-01-01 00:00:00.003

  9999-12-31 07:21:09.997 9999-12-31 00:00:00.000 1900-01-01 07:21:09.997

  9999-12-31 23:59:59.997 9999-12-31 00:00:00.000 1900-01-01 23:59:59.997

  More info here:

  Start of Time Period Functions

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

  Time Only Function: F_TIME_FROM_DATETIME

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358