sql to copy nvarchar to date field

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
LVL 1
AleksAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
?? 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
 
sdstuberCommented:
what platform?
0
 
AleksAuthor Commented:
MS SQL 2008
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kevin CrossChief Technology OfficerCommented:
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
 
AleksAuthor Commented:
Right .. I just don't know the syntax, not a real sql programmer hence the help
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
AleksAuthor Commented:
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
 
AleksAuthor Commented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.