Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql to copy nvarchar to date field

Posted on 2014-01-30
8
Medium Priority
?
450 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 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:amucinobluedot
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: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 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:amucinobluedot
ID: 39821272
:)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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