Solved

sql to copy nvarchar to date field

Posted on 2014-01-30
8
436 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
  • 4
  • 3
8 Comments
 
LVL 73

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
 

Author Comment

by:amucinobluedot
ID: 39820965
Right .. I just don't know the syntax, not a real sql programmer hence the help
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 57
Determine log file requirements 7 34
Substring() and LEFT() syntax 4 21
SQL Server Update Query Streamline 4 0
In this article I will describe the Detach & Attach 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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

16 Experts available now in Live!

Get 1:1 Help Now