Databases
StackOverflow2013
Postsdbo
POConvert Char field to DateTime but only when the field contains a date of format dd/mm/yyyy - Sql Server 2005
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConvert Char field to DateTime but only when the field contains a date of format dd/mm/yyyy - Sql Server 2005
    primarykey
    data
    text
    <p>I have a table: </p> <p><code>Comments (Id int identity primary key, PersonId Int(6), Comment1 Char(60))</code></p> <p>The field <code>Comment1</code> is supposed to contain nonessential comments <strong>only</strong>, however the field has been used to store nonessential comments but also important dates in the format dd/mm/yyyy.</p> <p>So for example, the data in the table appears like the following:</p> <pre><code>* Id * PersonId * Comment1 * | 1 | 5 | Likes Chocolate | | 2 | 5 | 19/05/1992 | | 3 | 5 | 23/07/1999 | | 4 | 5 | 05/06/1994 | | 5 | 8 | 07/12/1998 | | 6 | 8 | Is very Tall | | 7 | 8 | 24/05/1995 | | 8 | 8 | 16/10/2002 | | 9 | 11 | 13/11/2005 | | 10 | 11 | 21/09/2000 | | 11 | 11 | 8/99/100/23-1 | </code></pre> <h2><a href="http://sqlfiddle.com/#!3/5b621/4" rel="nofollow">SQL Fiddle</a></h2> <p>They now need to find the one row for each person which contains the most recent date in the <code>Comment1</code> field.</p> <p>Firstly I tried to remove those rows which did not contain a valid date using the following Query:</p> <pre><code>Select Id, PersonId, Case When IsDate(Comment1) = 1 Then convert(date, cast(rtrim(Comment1) as nvarchar), 103) Else NULL End From Comments </code></pre> <p>But this only returned me:</p> <pre><code>* Id * PersonId * Comment1 * | 4 | 5 | 1994-06-05 | | 5 | 8 | 1998-12-07 | </code></pre> <p>The other rows being <code>(null)</code>. This can be seen in the SQL Fiddle link.</p> <p>Could some one please tell me where I'm going wrong?</p> <p>Once this select statement returns what I require I expect I should be able to return the most recent date per <code>PersonId</code> using an aggregate function.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDMK
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COFYI, you don't need the `Else NULL` in your case expression - anything that isn't handled in one of your cases will return null anyway.
      singulars
      1. POConvert Char field to DateTime but only when the field contains a date of format dd/mm/yyyy - Sql Server 2005
      1. USBridge
    2. COThere's no guarantee that it won't attempt the `CAST()` even if `ISDATE()` returns 0 - it can rearrange operations. So you need to split this into two queries (a filter step and a comparison step) with a temp table or variable between the two. Also use `CONVERT()` so that you can pass an appropriate format value.
      singulars
      1. POConvert Char field to DateTime but only when the field contains a date of format dd/mm/yyyy - Sql Server 2005
      1. USDamien_The_Unbeliever
    3. COThe return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.
      singulars
      1. POConvert Char field to DateTime but only when the field contains a date of format dd/mm/yyyy - Sql Server 2005
      1. USAleksandr Fedorenko
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload