Solved

format nvarchar field as mm/dd/yyyy

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 27
Element alignment and word wrapping 9 24
Do we need servers??? 5 128
When to use an Aggregate Function. 18 34
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

707 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

12 Experts available now in Live!

Get 1:1 Help Now