SQL Server Performance

Convert date

Discussion in 'Getting Started' started by delbar, Jun 12, 2007.

  1. delbar New Member

    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
  2. FrankKalis Moderator

  3. delbar New Member

    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
  4. DilliGrg Member

    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)

  5. delbar New Member

    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
  6. satya Moderator

    http://www.karaszi.com/SQLServer/info_datetime.asp<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime1.asp>http://www.sql-server-performance.com/fk_datetime1.asp</a> &<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime2.asp>http://www.sql-server-performance.com/fk_datetime2.asp</a> by Frank on this site (how can I forget this [<img src='/community/emoticons/emotion-5.gif' alt=';)' />])<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  7. FrankKalis Moderator

    quote:Originally posted by delbar

    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


    DECLARE @dt NVARCHAR(20)
    SET @dt = '01012007'
    SELECT CAST(RIGHT(@dt, 4) + SUBSTRING(@dt, 3, 2) + LEFT(@dt, 2) AS DATETIME)

    Note that you might need to change the SUBSTRING and the LEFT, as I don't know what should be used for months and what for days. The format is done in your presentational layer, not in SQL Server.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  8. alzdba Member

    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 [?]
  9. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />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 [?]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><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>
  10. delbar New Member

    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
  11. FrankKalis Moderator

  12. alzdba Member

    <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>
  13. satya Moderator

    ISDATE is deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.

    The return value of ISDATE may be affected by LANGUAGE and DATEFORMAT settings.
    ISDATE returns 1 if the input expression is a valid date; otherwise, it returns 0.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  14. delbar New Member

    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
  15. Madhivanan Moderator

  16. delbar New Member

    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
  17. FrankKalis Moderator

  18. alzdba Member

    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 )
  19. sultan_i New Member

    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
  20. Adriaan New Member

    Sultan,

    Please start a new thread for new questions.
  21. FrankKalis Moderator

    quote:Originally posted by sultan_i

    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
    Please start a new thread for your question!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  22. Madhivanan Moderator

    http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=22203

    Madhivanan

    Failing to plan is Planning to fail

Share This Page