0

I am having date in 14/08/2014 12:33:50 PM( as TXNDate)

and i am getting result 2014-08-14 13:19:38.153 if I select current date using GetDate() function.

Now, my query is that I am having table 'EmpHistory' in which I want only those rows whose FromDate is '14/08/2014 12:33:50 PM'(TXNDate) but I have recorded FromDate in Table as '2014-08-14 13:19:38.153'

How do I can get result by Compare TXNDate with FromDate

talegna
  • 2,407
  • 2
  • 19
  • 22
Rupesh S
  • 125
  • 1
  • 14
  • What data type are you using to store the date values? It would be very helpful if you could post your table structure, the query you are trying to execute, and the expected output. – Dan Aug 14 '14 at 09:18
  • COntactID(bigint) MobileNo(varchar) FromDate(datetime) ToDate – Rupesh S Aug 14 '14 at 10:05
  • You might benefit from adding an [SQL Fiddle](http://sqlfiddle.com/) with an example of your data structure and content. What have you already tried? When is `TXNDate` being accessed? – talegna Aug 14 '14 at 15:52

1 Answers1

0

SQL seems to have problems determining which date format you are using. You can explicity tell it which format to use.

For a date in the format '2014-08-14 13:19:38.153' you can try "style" code 120. Like this:

Convert(datetime, '2014-08-14 13:19:38.153', 120)

For a date in the format '14/08/2014 12:33:50 PM', you can use the "style" code 103. Like this:

Convert(datetime, '14/08/2014 12:33:50 PM', 103)

MSDN has a full list of style codes and the corresponding numbers. http://msdn.microsoft.com/en-us/library/ms187928.aspx

tgolisch
  • 6,549
  • 3
  • 24
  • 42