Convert nvarchar to date

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

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

Select allOpen 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

Select allOpen 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.

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

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

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

Select allOpen 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:

if 06/10/2013 = Oct 6th:

further reading:
DATE and TIME ... don't be scared, and do it right (the first time)
SQL Server Date Styles (formats) using CONVERT()

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

Select allOpen 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

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

Of course! You don't need to ask. Cheers, Paul

Fantastic feedback from both Paul and Jim.