CAST or CONVERT = How to use?

Question

Hello!

I have a stored procedure which contains the follows:

 UPDATE dbo.LineItemInfo
      SET LIStatus = @Step,
      LIStateDate = @StatDateTaxDue

The problem I am having is that @StatDateTaxDue is an nvarchar(255) and

LIStateDate is a datetime datatype. How can I convert the date from a nvarchar(255) to a datetime in my stored proc? I can't see to get it figured out.

Thanks!
Mike

---

Mike Kiser

Answer 1

Use CAST() for now,. Th a en shoot the guy that passed a DATE as an insanely long string instead of the correct data type. He is dangerous.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 2

Thanks CELKO!

Yeah, I got stuck intregrating this lol (lucky me). I just tried

PStepDate = cast(@StatDateTaxDue as smalldatetime)

an example data from this column looks like

Sep 29 2019 12:00AM as an nvarchar

and it didn't work. Any suggestions? The datatype for this field that I am trying to update is smalldatetype. I meant to say in the above that LIStateDate is a smalldatetime datatype

Here is my error

Description: "The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.".

This is driving me crazy :-)

Thanks for your help!
Mike

---

Mike Kiser

Answer 3

You need to say

   convert(datetime, @StatDateTaxDue, nnn)

Where nnn is a format code for the format that @StatDateTaxDue is in. These codes are documented in the topic for Cast and Convert in Books Online.

As Joe Celko points out, the application should pass a datetime value. Only the application have knowledge about the user's regional settings.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks to both of you.

I just tried this. In the debugger the nvarchar field looks like this

Sept 5 2011 12:00AM

The code closest to this is 109 but I am still getting the same error. Any ideas? This is killing me :-)

 UPDATE dbo.LineItemInfo
      SET LIStatus = @Step,
      LIStateDate = convert(smalldatetime, @StatDateTaxDue, 109)
     
      WHERE LIPropCaseId = @CASEID

(I just tried 100 for the format code and it didn't work either)

Thanks!
Mike

---

Mike Kiser

Answer 5

No use for close matches here.  The strings must be exact matches of the style you select.  You will have to fix the data before proceeding with conversion.  Use the T-SQL string functions to accomplish the data cleansing.

---

Jose R. MCP
Code Samples

Answer 6

Your problem is going to be that when you let people enter the date as free form text, they will do all kinds of strange things.  Any one of them is easy to fix, the problem is your users are likely to do all sorts of strange things.  You have to determine what format they used, and convert it using string functions to a valid one that can be converted.  So for Sept 5 2011 12:00AM, you need to get rid of the t in the fourth column, so you could treat it as a special case and do something like

Declare @Test Table (InputDate nvarchar(255));
Insert @Test Select 'Sept 5 2011 12:00AM' Union All Select 'Jan 1 2013 9:33AM'
Select Convert(smalldatetime, 
  Case When Left(InputDate, 4) = 'Sept' Then Stuff(InputDate, 4, 1, '')
    Else InputDate End, 100)
From @Test;

But that only works until your users find a new format to enter the datetime.  Trying to handle every case will probably not be easy.

Tom

Answer 7

Please follow this MSDN link to learn about CAST or CONVERT and the different format codes that could be used for DATETIME conversion.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 8

That precise example works for me:

select cast('Sep 29 2019 12:00AM' as smalldatetime)

However, in another post you had Sept instead of Sep and that does not work.

The whole affair of interpreting strings as date is a difficult one, and it's even more difficult without a context. What date is 08/07/09?

For this reason, we prefer if the conversion from string to dates is performed at the source, close to the user, where we have the regional settings. The conversion routines on client-side also do a better job; SQL Server does a fairly poor job here. (Although this has been improved in SQL 2012 where you can use Parse and Try_parse from .Net.)

The particular format you have here is interesting, because it's SQL Server's default format when you convert date to strings, so may be this comes from a datetime column, but someone got the idea to store it in a string?

Whatever, your best action is to back to the source, and make sure that when the date enters SQL Server it is a datetime value.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

... Then shoot the guy that passed a DATE as an insanely long string instead of the correct data type. He is dangerous.

Although I disagreed with mister CELKO on using or not using the datatype BIT in another post, I applaud this remark. If you want to store a date, use the DATE datatype. If you want to store a dateless point in time, use the TIME datatype. If you want to store date and time, use the DATETIME2 datatype (or if you use an older version of MS SQL Server, the DATETIME datatype). That is what those datatypes are for. And don't pass date and/or time paramaters as strings.

Our company did build a Dutch ERP system 25 years ago. We started with passing dates as strings while storing them in a DATETIME field. We had all sorts of trouble. At most of our customers sites, the servers ran an English OS, having mm/dd/yyyy as the default way to translate a date to a string. Since our company is based in the Netherlands, and our ERP system started with only a Dutch version, the clients had a Dutch version of the OS, having dd/mm/yyyy as the default way to translate a date to a string. Certain parts of our software only seemed to work the first half of the month, or actually, only the first 12 days of the month. After that, it seemed to break down, but repair itself with the start of the next month. You already guessed it, it did only work properly on 12 days every year: 01/01/yyyy, 02/02/yyyy, ... 12/12/yyyy. And also, we wanted to store dates, but we used DATETIME fields. Sometimes, some field managed to get filled with a time part not beeing 00:00:00. You don't want to know the trouble we had to build queries that showed exactly the right data for a date range.

Answer 10

Certain parts of our software only seemed to work the first half of the month, or actually, only the first 12 days of the month. After that, it seemed to break down, but repair itself with the start of the next month.

The users who used those parts must have loved it. The always got the second half of the month off. :-)

And also, we wanted to store dates, but we used DATETIME fields. Sometimes, some field managed to get filled with a time part not beeing 00:00:00. You don't want to know the trouble we had to build queries that showed exactly the right data for a date range.

In the system I work most with we have lots of dates. This system is far older than the date data type in SQL Server. So we use this data type:

EXEC sp_addtype 'aba_date', 'datetime'
go
CREATE RULE aba_date_rule AS convert(char(8), @x, 112) = @x
go
EXEC sp_bindrule 'aba_date_rule', 'aba_date'
go

It has certainly stopped confused programmers at times.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

>> Yeah, I got stuck integrating this lol (lucky me). <<

No good deed goes unpunished :)

CONVERT() is an old string function and it assumes that the input is “reasonable” as a leap of faith.  I had a non-T-SQL job where we built a table with a DATE column as the key, then 8 or 9 columns with all the screwed up strings for dates we could find. The front ends (plural) had no programming standards. 

We did not even try for a regular expression since; we only needed ten years.  It was easier to use spreadsheet to get most of the strings, but some were done manually.  To quote Wikipedia: “The names of Czech months are, as in Polish, Croatian, Ukrainian and Belarusian not based on the Latin names used in most European languages” to mess  you up. And at one time NATO used Roman numerasl for months to keep the french happy. Arrrgh!  

Please USA, go Metric & ISO before I dies!

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 12

Thanks EVERYONE for your responses....

After seeing what I am in for, we are having a con call with our Team in India about changing the date to smalldatetime from nvarchar as it should be.  :-)

Mike

---

Mike Kiser

Answer 13

HI ALL!
I have this working in the Query Window but I don't know how to assign the value which the SELECT outputs into a variable in my stored Proc. I want to assign it to @result so that I can update a column in a table...THANKS! MIke

USE BAPropertyTax

declare @date_val nvarchar(8)
set @date_val='22/10/23'
declare @result smalldatetime

select cast(
  case when left(@date_val,2)<50 then '20'
  else '19' end+left(@date_val,2)+'-'+substring(@date_val,4,2)+'-'+right(@date_val,2)
  as smalldatetime
 )

---

Mike Kiser

Answer 14

You can use a SET statement, eg

declare @date_val nvarchar(8)
set @date_val='22/10/23'
declare @result smalldatetime

Set @result = cast(
  case when left(@date_val,2)<50 then '20'
  else '19' end+left(@date_val,2)+'-'+substring(@date_val,4,2)+'-'+right(@date_val,2)
  as smalldatetime
 ) 

Or if all you want to do with this is update a column in a table, you can do that without putting the result in a variable, just use the expression in your UPDATE statement.  That would look something like

declare @date_val nvarchar(8)
set @date_val='22/10/23'

update your_table_name
set your_column_name = cast(
  case when left(@date_val,2)<50 then '20'
  else '19' end+left(@date_val,2)+'-'+substring(@date_val,4,2)+'-'+right(@date_val,2)
  as smalldatetime
 ) 
where <condition that identifies the row(s) you want to update>

Tom

Answer 15

Tom,

That worked perfectly!! Thanks!!
Mike

---

Mike Kiser