Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

format nvarchar field as mm/dd/yyyy

Posted on 2016-09-19
4
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 1000 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 53

Accepted Solution

by:
Ryan Chong earned 1000 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 49

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

688 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