Solved

format nvarchar field as mm/dd/yyyy

Posted on 2016-09-19
4
62 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 49

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now