Solved

format nvarchar field as mm/dd/yyyy

Posted on 2016-09-19
4
78 Views
Last Modified: 2016-09-19
I have table 'users'  and field 'dob'
dob is an navarchar field and has the following type of values:   Aug  1 1968 12:00AM

I need to run a query to format such values as if they are dates, so it should read '08/01/1968'

How can I do this ?
0
Comment
Question by:amucinobluedot
4 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 250 total points
ID: 41805719
You can use the ISDATE() function to determine if the input value is, in fact, a date. Something like this perhaps:
SELECT CASE WHEN (ISDATE('Aug  1 1968 12:00AM') = 1) THEN CONVERT(VARCHAR, CAST('Aug  1 1968 12:00AM' AS DATETIME), 101) ELSE '' END

Open in new window

The issue here is what do you want it to do when the value is not a date?

A couple of side notes:
- It's not a good practice to store date values in anything other than DATE, DATETIME, or DATETIME2 fields.
- It's not a good idea to format output from a database query. That sort of thing is best done in either the business logic or presentation layer of an application.
0
 

Author Comment

by:amucinobluedot
ID: 41805738
I know. I am cleaning up a database that is not mine and converting the nvarchar to datetime field.

I tried to make it generic using the field name but I saw there is no update in the query. I need to UPDATE the value.

SELECT CASE WHEN (ISDATE(dob) = 1) THEN CONVERT(VARCHAR, CAST('dob' AS DATETIME), 101) ELSE '' END

Open in new window


I need to select it FROM USERS table and update it.

Something like

UPDATE USERS  CASE WHEN (ISDATE(dob) = 1) THEN CONVERT(VARCHAR, CAST('dob' AS DATETIME), 101) ELSE '' END

Open in new window

0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41805760
>>I need to select it FROM USERS table and update it.
it's advised that do not amend your original data and populated the converted values into a new column in the same or new table.

try this if you insert the date into new column in same table:
UPDATE a Set
a.DOB_new =   CASE WHEN ISDATE(a.dob) = 1 THEN CONVERT(VARCHAR, CAST(a.dob AS DATETIME), 101) ELSE NULL END
from USERS a

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41805820
FYI if you have SQL Server 2012 onward you can use TRY_CAST()

Update new_column = try_cast(old_column as datetime)

if the cast can be performed it returns datetime if it cannot convert it returns NULL

This way you avoid needing to use specific date styles like 101, 112  and so on.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamic SQL select query 4 38
MS SQL query to show nearest date 6 41
Parse this column 6 27
SQL Recursion schedule 13 18
Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question