?
Solved

sql to copy nvarchar to date field

Posted on 2014-01-30
8
Medium Priority
?
452 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:Aleks
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39820810
what platform?
0
 

Author Comment

by:Aleks
ID: 39820812
MS SQL 2008
0
 
LVL 60

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

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:Aleks
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 60

Accepted Solution

by:
Kevin Cross earned 2000 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:Aleks
ID: 39821272
:)
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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