asked on

Reformat date/time entry in nvarchar field

I currently have a field which is 'ExpiresOn' it is a varchar (25)

Sometimes user will enter a date and sometimes something else like "NA".
Problem I had is we added date & time in that field so then it displays in the database as "May 11 2011 12:00AM"

I need a query that will reformat any entry that is a date time with the format above to the following format: mm/dd/yyyy, using the example above it would be 05/11/2011

Help is greatly appreciated. Example of entries in my DB attached.

Table name is "Users"
Field name is: "ExpiresOn"
screenshot.PNG

SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

That only made a selection. I need to update the value

Aleks

ASKER

Any other ideas ? It didn't reformat the fields :$

Vitor Montalvão

How are you inserting the date values? Also, there's a record with 'DS'. What that means?

Aleks

ASKER

I am not worried about the insert, that was fixed. I want to fix the entries that are already in the database.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

I apologize I am trying to meet a deadline ... let me test this. Ill try and learn how to fish next time around.

Aleks

ASKER

I first tried a select with your code but there is a syntax error:

SELECT UserId ,
ExpiresOn
FROM dbo.Users
WHERE ISDATE(ExpiresOn)
ORDER BY ExpiresOn DESC

--- error---

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Thanks