Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

format nvarchar field as mm/dd/yyyy

Posted on 2016-09-19
4
Medium Priority
?
118 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:Aleks
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:Aleks
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 54

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
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.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

916 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