Convert nvarchar to date

Hi All

This will be my last question for today as I need to do some studying!!

I referred to this in a previous question, but now need to know how to do it.  I'm importing from Excel into SQL Server and it brings the dates in as nvarchars.  I'm then performing some stuff on those date but the results are incorrect and I think it might be because they are nvarchars not dates.

I've tried various ways of converting them but have had no success.  Please can someone give me some guidance?

Thanks
Sarah
ScuzzyJoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
please paste the sample data in your table along with the operation you are trying to perform and the error .
I think you should use the CONVERT function to convert the varchar dates to a valid date

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).ASPX
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
These will convert all of the nvarchar's that are in date format to datetime (date?)
SELECT blah, blah, blah, CAST(the_nvarchar as datetime) 
FROM your_table
WHERE ISDATE(the_nvarchar) = 1

Open in new window

These will identify all of the columns that can't convert to a date, either because it's February 31st, or 42, or 'banana'.   You'll have to figure out how to update this data so that it can be convertable to a date.
SELECT blah, blah, blah, CAST(the_nvarchar as datetime) 
FROM your_table
WHERE ISDATE(the_nvarchar) = 0

Open in new window

also fyi date is the date only with no time component, and datetime contains both date and time.

also fyi Excel is a wonderful tool for users to edit darn near anything, but a terrible data source if you expect it in a certain format as users can edit darn near anything.
0
ScuzzyJoAuthor Commented:
Hi

Thanks for replying.  I'm not absolutely sure how to use it, even having read the page.  It seems to use a lot variable and I want to covert the data in a column.  At the moment, it looks like, e.g., 06/10/2013 but it doesn't find the max or min correctly.

I also don't really understand Transact-SQL (which is what this says it is) as I've only been using SQL Server since Thursday afternoon.

Thanks
Sarah
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ScuzzyJoAuthor Commented:
Hi Jim

I've had a go at this but am not sure what the blah, blah, blah bit should be.  I tried * as follows:

SELECT * CAST(Pres_Start as datetime)
FROM T_1213_OnlySSN
WHERE ISDATE(Pres_Start) = 1

but got:

Incorrect syntax near 'CAST'.

as an error.

I'm probably missing something here :-)

I agree with you about Excel, but this is a download from our main database using SAS and it doesn't get butchered between there and going into SQL (unless I butcher it - lol!)

Thanks
Sarah
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I was just using blah blah blah to illustrate the rest of the SELECT query.
To keep it simple, to just view the column we're talking about..  
SELECT CAST(Pres_Start as datetime) 
FROM T_1213_OnlySSN
WHERE ISDATE(Pres_Start) = 1

Open in new window

Just a thought, if you're importing from EXCEL to SQL, a better idea would be to initially import everything into character-type columns, which insures that the data makes it to SQL.

Then in SQL, you can do your converting to handle dates, numbers, etc.

>but this is a download from our main database using SAS
If this is a .csv then it's probably fine.   If it's actually Excel, chance are you'll be able to reach out and touch someone, and get them to change the file to some kind of text file, either fixed width or delimeted.
0
PortletPaulfreelancerCommented:
You don't mention if 06/10/2013 is June 10th or October 6th because 06/10/2013 is "ambiguous". This potential for ambiguity needs to be resolved too and instead of relying of defaults I'd suggest you need to use the CONVERT() function which accepts a date 'style' as a parameter.

The date sequence MM/DD/YYYY has a style number of 101 in TSQL
The date sequence DD/MM/YYYY has a style number of 103 in TSQL

You can execute the following to see the difference:
DECLARE @nvarchar_as_date AS nvarchar(10)

SET @nvarchar_as_date = '06/10/2013'

SELECT

      @nvarchar_as_date                        as input

    , isdate(@nvarchar_as_date)                as [can be converted?]

    , convert(datetime,@nvarchar_as_date, 101) as jun_10_2013
    , convert(datetime,@nvarchar_as_date, 103) as oct_6_2013

Open in new window

>>I'm then performing some stuff on those date but the results are incorrect and I think it might be because they are nvarchars not dates.
If the "stuff" is date aritmetic or things like MIN() and MAX() then yes - it will be because they are NOT dates at all - they just look like dates (there's a big difference).

Unfortunately a nvarchar column that looks like dates can also hold other information that will not convert e.g. 30/02/2013 ooops, looks like a date but isn't a date, even 31/06/2013 could be entered.

So, when converting that column to information SQL Server will understand as real dates sometimes you may hit bad inputs (like 30/02/2013) and it will bomb out with an error. To help overcome that the ISDATE() function may be used. If that function returns 1 it can be converted to date, other wise it returns 0 and a value cannot be converted to date.

So, if 06/10/2013 = June 10th:

SELECT convert(datetime,Pres_Start, 101) as Pres_Start_dt
FROM T_1213_OnlySSN
WHERE ISDATE(Pres_Start) = 1
if 06/10/2013 = Oct 6th:

SELECT convert(datetime,Pres_Start, 103) as Pres_Start_dt
FROM T_1213_OnlySSN
WHERE ISDATE(Pres_Start) = 1
further reading:
DATE and TIME ... don't be scared, and do it right (the first time)
SQL Server Date Styles (formats) using CONVERT()
0
ScuzzyJoAuthor Commented:
Hi All

I've spent some time going through this and have been exporting the files into Excel to check what's going on.  I think I'm still doing something wrong :-(

I've tried using both cast (Jim) and convert (Paul) and they're both fine, but something isn't right somewhere.  Having said that, where does it put the Pres_Start_dt bit from the below?  It doesn't add a column, so how do I refer to it?  The relevant bit of code is:

--Convert the Presentation Start date to a date in the Only SSN table
SELECT convert(date,Pres_Start, 103) as Pres_Start_dt
FROM T_1213_OnlySSN
WHERE ISDATE(Pres_Start) = 1
--Create a table containing the earliest Presentation Start Date for each student
SELECT pi, MIN(Pres_Start) as min_date
INTO T_1213_SAYDate
FROM T_1213_OnlySSN
GROUP BY pi;
--Convert the Presentation Start date to a date in the SAY (Seasonal Academic Year) table
SELECT convert(date,min_date, 103) as Pres_Start_dt
FROM T_1213_SAYDate
WHERE ISDATE(min_date) = 1

Open in new window


I did change datetime to date as I didn't want the time in there, but I didn't get any errors coming up.

It all runs fine but, when I export both OnlySSN and SAYDate into Excel to take a look, SAYDate isn't picking up the first date in all cases.

I can butcher (sensitive data) and post the tables if needs be, but I don't really know how to other than as Excel or csv files.

BTW, Jim, I tried importing a csv but it went completely wrong!  I'd like to sort this issue out first and then come back to that.  The file actually arrives as an Excel file and I can't really get that changed as it has a whole load of summary data attached for other users.  I don't want to get a separate file as we did that last year and ended up with different result to each other.

Thanks
Sarah
0
PortletPaulfreelancerCommented:
Once you convert to date, and place that into a table, you do not need to keep converting it.
Try this:
--Create a table containing the earliest Presentation Start Date for each student
SELECT pi, MIN(Pres_Start_dt) as min_date
INTO T_1213_SAYDate
FROM (
        SELECT pi, convert(date,Pres_Start, 103) as Pres_Start_dt
        FROM T_1213_OnlySSN
        WHERE ISDATE(Pres_Start) = 1
     ) as ssn
GROUP BY pi
;

--Now no need to Convert the Presentation Start date to a date in the SAY (Seasonal Academic Year) table
SELECT min_date
FROM T_1213_SAYDate
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScuzzyJoAuthor Commented:
Hi Paul

That works perfectly, thanks.  I'm pretty sure I understand what's going on here too, so I'm happy.

Jim was also helpful, so I'd like to allocate some points to him as well if that's OK with you?

Thanks
Sarah
0
PortletPaulfreelancerCommented:
Of course! You don't need to ask. Cheers, Paul
0
ScuzzyJoAuthor Commented:
Fantastic feedback from both Paul and Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.