Converting datetime stamp to Date time in Sql 2005

• suryam

  Mr or Mrs. 500

  Points: 549

  More actions

  August 6, 2014 at 12:03 am

  #310546

  Hello ,

  Can anybody help me on this simple data extraction from a table.

  E.g

  i am having a table where i am keeping SMS logs sent to investors. we are maintaining a date field when the sms has sent with time (e.g 2014-07-24 05:17:59.043). Now My requirement is to extract sms logs sent on a particular date by taking parameter as date field mentioned above. suppose i want yesterday how many SMS has sent. so i m using below script

  1st try:

  select * from sms_processedfeeds

  where pf_fund='108'

  and pf_trtype='SIPDEBALT2'

  AND pf_entdt >='08/05/2014'

  AND pf_entdt < ='08/05/2014'

  2nd try:

  select *

  from sms_processedfeeds

  where pf_fund='108'

  and pf_trtype='SIPDEBALT2'

  AND convert(varchar(10), pf_entdt ,10)>= dateadd(dd,-1,'08/05/2014' )

  AND convert(varchar(10), pf_entdt ,121) < = '08/05/2014

  But i am not geting proper output

  Can any body help me out on ths

  Thanks in advance.

  Thanks
  SUrya

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  August 6, 2014 at 12:36 am

  #1735496

  SELECT *

  FROM sms_processedfeeds

  WHERE pf_fund='108'

  AND pf_trtype='SIPDEBALT2'

  AND pf_entdt >= '08/05/2014 00:00:00'

  AND pf_entdt < '08/06/2014 00:00:00'

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP