Convert date

I am working with a table that stores a date as nvarchar, 01012007, and would like to convert to smalldatetime, 01/01/2007.

Using the following in storedprocedure:

CONVERT(smalldatetime, right([DATE-OF-SVC],4) + Left([FIRST-DATE-OF-SVC],4), 101) as DateFilled

Receiving an error referencing arithmetic overflow, cannot convert.


can someone help me out with this?

thanks,
bobbi


Bobbi

thanks for responding so quickly. I tried Cast([DATE-OF-SVC] AS DATETIME and still getting the same error:

Arithmetic overflow error converting expression to data type datetime.



Bobbi

quote:Originally posted by delbar

thanks for responding so quickly. I tried Cast([DATE-OF-SVC] AS DATETIME and still getting the same error:

Arithmetic overflow error converting expression to data type datetime.



Bobbi

Why do you want to convert to smalldatetime? Try using DATETIME instead while converting into. Otherwise, what are your datatypes for DATE-OF-SVC, FIRST-DATE-OF-SVC?




Name
---------
Dilli Grg

(1 row(s) affected)

The data type is nvarchar and the date is entered as 01012007 (mmddyyyy). I'd like to change this to show a date format of 01/01/2007.

Tried using datetime and still getting same error message.

thanks!

Bobbi

how about using ISDATE ?<br /><br />DECLARE @datestring varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />SET @datestring = '12/21/98'<br />SELECT ISDATE(@datestring)<br /><br />Check BOL<br /><br />Select *<br />from yourtable<br />where isdate([your_date_column]) = 0 [?]

thanks much ... tried everything but still getting the same error. Not sure why this is happening but I need to convert this from nvarchar to datetime in order to get calculations on datadd etc.

If you have any more suggestions, appreciate.



Bobbi

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Unfortunately the format right now seems to be more like 122198 and not 12/21/98. <br /><br />Btw, doing overtime or just surfing and hanging around here? [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Actualy it's the combination.<br />Doing overtime because our EVA-SANs got stuck whilest activating multichanel. [B)][xx(]<br />I was awaiting the solution from our SAN-guy (He succeeded !) and we got our server back up and running. <br />In the meanwhile I was checking my forums [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />It was very frightening when the first node of our 64-bit W2K3 cluster refused to come online [B)] until we also tried to start the second node. Then it went fine.<br />We'll be contacting MS to clarify this issue !<br /><br /><b>btw maybe the isdate will function using substrings to place the / in the string</b>

thanks to all. I am going to use the isdate (thinking there are some entries that are not actual dates).

I am really a newbie but will search for isdate.

thanks,
bobbi

Bobbi

hello again,

used isdate and discovered that there were entries that were not dates 00000000. Filter the 0's and used the cast function but am still getting arithmetic overflow.

here is my sql statement:
Create table #tblTemp (
subno char (14) null,
persno char (2) null,
subnopers char (16) null,
DateFilled nvarchar (50) null,
DaysSupply nvarchar (15) null,
NDC nvarchar (25) null,
Drug char (55) null
)

insert into #tblTemp
SELECT DISTINCT m.Subno,
m.Persno,
RTRIM(m.subno) + m.persno AS subnopers,
c.[FIRST-DATE-OF-SVC] as DateFilled,
c.[DAYS SUPPLIED] as DaysSupply,
c.[NATIONAL DRUG CODE] as NDC,
c.[DRUG NAME] as Drug
FROM [dbo].[membr] m
INNER JOIN [MEDS].[dbo].[tblCHPLUSArchive] c
ON m.[udef4] = c.[RECIP CARDHOLDER ID]
WHERE NOT [FIRST-DATE-OF-SVC] = '00000000'


Create table dbo.tblAsthmaPharm (
subno char (14) null,
persno char (2) null,
subnopers char (16) null,
DateFilled datetime null,
DaysSupply nvarchar (15) null,
NDC nvarchar (25) null,
Drug char (55) null
)

insert into dbo.tblAsthmaPharm
SELECT DISTINCT m.Subno,
m.Persno,
RTRIM(m.subno) + m.persno AS subnopers,
Cast(t.DateFilled as datetime),
c.[DAYS SUPPLIED] as DaysSupply,
c.[NATIONAL DRUG CODE] as NDC,
c.[DRUG NAME] as Drug
FROM [dbo].[membr] m
INNER JOIN [MEDS].[dbo].[tblCHPLUSArchive] c
ON m.[udef4] = c.[RECIP CARDHOLDER ID]
INNER JOIN #tblTemp t on m.subno = t.subno and m.persno = t.persno
WHERE t.DateFilled >=DATEADD(year, - 2, GETDATE())



*********I need to convert DateFilled to datetime ****************

Bobbi

indeed, maybe an isnumeric might also be in place

find faulty with something like
where isnumeric(datecolumn) = 0
or not (substring(datecolumn, 1,2) between '01' and '31' -- check days
and substring(datecolumn, 3,2) between '01' and '12' -- check month
and substring (datecolumn, 5,4) between '2000' and '2007' -- years )

Hi all

Here I am doing some analysis in sql server performance monitoring. From development team I got contextswitches counter maximum values is like 175461


Is there any possibility to get maximum values is 175461 ?. what is the maximum limit for contextswitches counter ?. While I am running, I am getting maximum up to 10,000. please give your suggestion .

regards
sultan

Sultan

Sultan,

Please start a new thread for new questions.