Convert date | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
Set the dateformat to the appropriate one, do a simple CAST() and that should be it:
SET DATEFORMAT mdy
…CAST([DATE-OF-SVC] AS DATETIME)

Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
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
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>
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
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 [?]
<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>
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
Can you post your statement and some sample data where it fails? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
<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>
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.
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
Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164 Madhivanan Failing to plan is Planning to fail
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
Can it be that there is some more invlaid data in that column? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
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.
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
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=22203 Madhivanan Failing to plan is Planning to fail
]]>