0

I am trying to convert data imported into my app from nvarchar to date so I can compare the date with the current system date to ensure that past dates are not used.

The data is imported through the app into generic table where all columns are nvarchar (process cannot be changed), this results in me needing to convert the data type to compare the field with a date.

I have tried the following code to convert the date (TestData representing the column I am using in this instance):

CAST(TestData as date)
CONVERT(date, TestData)

Which is giving me an error message along the lines of: Cannot convert nvarchar to date.

Guessing I am missing something easy here, thought I'd try my luck here.

egerardus
  • 11,316
  • 12
  • 80
  • 123
TonyTightTop
  • 37
  • 1
  • 7
  • 1
    `CONVERT` accepts a third parameter to specify the format. `PARSE` is another option, if you know the culture of the date. – Jeroen Mostert Jun 24 '19 at 19:53
  • Date will be dmy. Will have a look into the PARSE function. Thanks – TonyTightTop Jun 24 '19 at 19:55
  • SQL tag is not about SQL Server on this website, it is about ANSI/ISO standard SQL...Sounds like your are talking about SQL Server here.. – Raymond Nijland Jun 24 '19 at 19:55
  • "Date will be dmy.", maybe add some sample data so we know if you mean a date with a 2 or 4 digit year for instance. – Joakim Danielson Jun 24 '19 at 20:00
  • Possible duplicate of [European format time - Convert string to Datetime in SQL](https://stackoverflow.com/questions/8844153/european-format-time-convert-string-to-datetime-in-sql). You just need to add the style parameter to `CONVERT` and you should be in business. – Eric Brandt Jun 24 '19 at 22:14
  • Possible duplicate of [Sql Server string to date conversion](https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) – egerardus Jun 25 '19 at 04:04
  • the answer is: `select convert(datetime,'24-06-2019',105)`, voting to close this as duplicate of [this one](https://stackoverflow.com/a/7175369/1062992) – egerardus Jun 25 '19 at 04:08

3 Answers3

0

Assuming the date format coming in is always 'DD-MM-YYYY' like you said in one of your comments:

DECLARE @data NVARCHAR(250)

SET @data = '24-06-2019'

SELECT CONVERT(DATE, @data, 103) AS [Date] -- 104 or 105 will work as well

Result: 2019-06-24

  • 103 - British/French Standard (dd/mm/yyyy)
  • 104 - German Standard (dd.mm.yyyy)
  • 105 - Italian Standard (dd-mm-yyyy)
Nathan Friedman
  • 5
  • 4
0

Event if you want to convert your varchar field into datetime format, your input record must be in proper format. Chances are your data in varchar field is not in datetime format. May be some values are NULL. Please check your records once.

Or

Your given input is not in appropriate format. Use convert function to convert datetime with its respective date format.

Please find this link https://www.w3schools.com/sql/func_sqlserver_convert.asp for your reference of convert.

DarkRob
  • 3,843
  • 1
  • 10
  • 27
-1

Can you give an example of how your nvarchar data is looking like? I wrote this example to show you how to compare a nvarchar(200) variable to the current date but that nvarchar is a simple string value that is formatted in the correct way that SQL will understand (hence the reason i am asking an example from your side ;-) )

Use Master
go
declare @Dates as nvarchar(200)
set @dates = '23 jan 2019'
--set @dates = '23/01/2019' --This one will generate a Conversion Failed
--set @dates = '01/23/2019' --This one will NOT generate a Conversion Failed
Select Cast(@dates as Date) 'Casted', GetDate() 'Today',DateDiff(d,Cast(@dates as Date),GetDate()) 'Days'
Gremlin1708
  • 91
  • 1
  • 1
  • 13