Solved

sql to copy nvarchar to date field

Posted on 2014-01-30
8
446 Views
Last Modified: 2014-01-30
I have a table called 'users'

an nvarchar (25) field named 'visaexp'

and a datetime field named 'visaexpd'

The visa exp may have data such as :  "NA" or "01/01/2012" etc. Some are actual dates.

I need to run a script that will copy the data from visaexp to visaexpd IF the value is a date
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
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39820810
what platform?
0
 

Author Comment

by:amucinobluedot
ID: 39820812
MS SQL 2008
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39820940
For SQL Server, you can use ISDATE() function to assist you.  Basically filter the data to records with ISDATE(visaexp) = 1, then UPDATE visaexpd = CONVERT(DATE, visaexpd).

EDIT: here is a demonstration.
DECLARE @users TABLE(
    id BIGINT IDENTITY,
    visaexp NVARCHAR(25),
    visaexpd DATE,
    PRIMARY KEY(id)
);

INSERT INTO @users(visaexp)
VALUES('NA'),
      ('01/01/2012'),
      ('01/30/2014'),
      (NULL)
;

UPDATE @users
SET visaexpd = CONVERT(DATE, visaexp, 101)
WHERE ISDATE(visaexp) = 1
;

SELECT id, visaexp, visaexpd
FROM @users
;

Open in new window


The results:
id                   visaexp                   visaexpd
-------------------- ------------------------- ----------
1                    NA                        NULL
2                    01/01/2012                2012-01-01
3                    01/30/2014                2014-01-30
4                    NULL                      NULL

Open in new window


MSDN: http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:amucinobluedot
ID: 39820965
Right .. I just don't know the syntax, not a real sql programmer hence the help
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39820988
I was editing my comment to add a more concrete example.  Please refresh my comment above for syntax help as well as Microsoft reference on CONVERT() function.

Here is the reference for ISDATE:
http://technet.microsoft.com/en-us/library/ms187347(v=sql.105).aspx

Note: I showed CONVERT with format code 101 (U.S. - mm/dd/yyyy) as an example, but you can leave this off or use a different code.  Just be sure the consistency of your data, so that the resulting date is what you intended.
0
 

Author Comment

by:amucinobluedot
ID: 39821000
Yeah .. the example doesn't really help me. I actually need the query that would copy the data from the tables I have. Ill wait for someone to create the query based on the table and fields info.

Something like:

Update users
set visaexpD = visaexp

IF .. visa exp is a date

Should be something simple.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39821174
?? I showed you the exact query you need above.  I do not have your table, so I had to use an example table @users.  If you replace "@users" with "users" in the following query, you have the exact syntax you need.

In other words, this is the syntax if your dates are in U.S. "mm/dd/yyyy" format.
UPDATE users
SET visaexpD = CONVERT(DATE, visaexp, 101)
WHERE ISDATE(visaexp) = 1
;

Open in new window


What else do you need to see or have explained?  The elements are pretty straight-forward.  The ISDATE(...) handles the IF visa exp is a date part, and the CONVERT(...) handles explicitly setting visaexp to DATE data type.  You can use implicit conversion, but I find it better to be specific.  It not only avoids errors but also provides clarity when you come back to the code later.

However, this also works in many cases:
UPDATE users
SET visaexpd = visaexp
WHERE ISDATE(visaexp) = 1
;

Open in new window


I hope that helps!
0
 

Author Closing Comment

by:amucinobluedot
ID: 39821272
:)
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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