Datepart combining into one filed

• DHeath

  SSCertifiable

  Points: 5200

  More actions

  January 23, 2009 at 6:30 pm

  #73437

  My apprecaition is great to all that take time to read and give input 🙂

  Help.... if i can get some help on the below script it would be much appreciated. Also if there is a faster way to run this same thing witn same results on a table with million plus rows. Thanks

  my query below: Works but return value is not right

  SELECT DISTINCT

  ORDER_NUM, CAST(DATEPART(yyyy, OPERATION_DT) AS char) + '-' + CAST(DATEPART(m, OPERATION_DT) AS char) + '-' + CAST(DATEPART(dd,

  OPERATION_DT) AS char) AS Expr3

  FROM (SELECT TOP 100 *

  FROM ClosedOrders_AT WITH (nolock)

  ORDER BY OPERATION_DT DESC) DERIVEDTBL

  returns something to this manner

  order_num {This is one massive field and i want it smaller}

  1-15773563762009 1 23

  i am going for this

  order_num date

  1-1577356376 2009-1-23

  Thanks and hopefully i gave enough info.....

  Darryl

  DHeath

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  January 24, 2009 at 5:57 am

  #932200

  Please read Cast and Convert topic in Book On Line.

  Especially the part about styles for datetime-to-varchar conversions.

  Your answer is right there.

  _____________
  Code for TallyGenerator

• austin.wimberly

  Valued Member

  Points: 73

  More actions

  January 27, 2012 at 9:46 am

  #1439483

  On a similar subject when I run this query...

  select cast(DATEPART(yyyy, getdate() - 90) as char(4))+cast(DATEPART(mm, getdate() - 90) as char(2))+cast(DATEPART (dd, getdate() - 90) as char(2))+cast(DATEPART (hh, getdate()) as char(2))+cast(DATEPART (mi, getdate()) as char(2))+cast(DATEPART (ss, getdate()) as char(2))+'.000000-480'

  {201110298 2330.000000-480}

  yyyy - 2011

  mm - 10

  dd - 29

  hh - it should be 08

  mi - 23

  ss - 30

  I cannot figure out how to get rid of that space, and get it to put in the 0 for the hour. Should I put this in hh24?

• austin.wimberly

  Valued Member

  Points: 73

  More actions

  January 27, 2012 at 2:05 pm

  #1439613

  Well I resolved my issue, so I thought I would post it here:

  I went back and researched the date formats, and came up with this query:

  SELECT REPLACE (convert(varchar, getdate() -90, 111), '/','') + REPLACE (convert (varchar, getdate(), 108), ':','') +'.000000-480'

  This gives me the WMI, UTC, date that I need for my other WMI queries. I hope this helps someone else too.