How to convert text to date?

• NineIron

  SSChampion

  Points: 12529

  More actions

  May 11, 2015 at 8:49 am

  #398436

  I can't figure out why this won't work. POST_DT is nvarchar(50). I know it should be datetime but, I have no control over it. That's why I'm creating a temp table. But, I want to only insert the most recent invoice. Any thoughts? There is code to create test data at the end.

  create table #FSC589

  (

  GRP__2 char(1),

  INV_NUM char(8),

  MRN varchar(10),

  POST_DT datetime

  )

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT)

  select GRP__2, INV_NUM, MRN, max(convert(datetime, POST_DT, 121))

  from IDX_INCOME

  where INV_FSC__1='CREDIT APPROVED'

  group by GRP__2, INV_NUM, MRN, POST_DT

  test data

  create table #FSC589

  (

  GRP__2 char(1),

  INV_NUM char(8),

  MRN varchar(10),

  POST_DT datetime

  )

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17977646','83894','05/31/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/08/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/08/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899774','90591','05/08/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17918283','88926','05/23/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17940377','88926','05/10/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17940377','88926','05/10/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17941937','92866','05/11/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911367','96040','05/20/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911367','96040','05/20/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921469','96040','05/12/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17944086','96040','05/12/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17944086','96040','05/12/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17955465','94766','05/19/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17962333','94766','05/22/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17967631','94766','05/24/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17794582','94798','05/30/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17901511','103779','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17901511','103779','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','15758688','697868','02/23/2011')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17928953','94725','05/03/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17928953','94725','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17199540','117798','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17223802','117798','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17238636','117798','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17420392','117798','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17567282','117798','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17830827','117798','05/19/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17830827','117798','05/19/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17933335','117798','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17954406','117798','05/19/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960518','117798','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17980032','117798','05/30/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17958747','100601','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920488','681846','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920488','681846','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17882995','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17882995','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17888908','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17888908','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17889801','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17889801','102297','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960408','102297','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960408','102297','05/28/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960410','102297','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960410','102297','05/28/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960413','102297','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960414','102297','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976750','102297','05/30/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911493','117950','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17974201','78795','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17974201','78795','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17906030','68222','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17922194','68222','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17922194','68222','05/16/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17977689','98177','05/30/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17622709','104619','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17622709','104619','05/07/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920966','104619','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920966','104619','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921050','104619','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921050','104619','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17955484','104619','05/19/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17963614','104619','05/22/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17968169','104619','05/25/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976601','104619','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976601','104619','05/29/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17249407','855691','06/26/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17249407','855691','06/26/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17208515','32139','04/05/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17235554','32139','04/20/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17235554','32139','04/22/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17236850','32139','04/22/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17236850','32139','04/22/2013')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/09/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17907257','81694','05/13/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17907257','81694','05/13/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921233','81694','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921233','81694','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17950253','81694','05/15/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976893','81694','05/30/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17873961','44219','05/07/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17883058','44219','05/14/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/02/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/14/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/07/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/07/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/08/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/08/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/21/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903924','44219','05/09/2014')

  insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903924','44219','05/09/2014')

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 11, 2015 at 9:08 am

  #1796976

  Remove POST_DT from the GROUP BY.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• NineIron

  SSChampion

  Points: 12529

  More actions

  May 11, 2015 at 9:21 am

  #1796979

  That was easy! Thanx.