|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with varchar to datehold credit card expiry dates in mm/yy format e.g 10/06 I want to update a new column with a smalldatetime value derived form the column above. So I tried the following CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) RETURNS smalldatetime AS BEGIN --want to be sure it is interpreted as dd-mm-yy format RETURN CONVERT(smalldatetime, '01-' + CASE CONVERT(tinyint, Left(@DATETEXT, 2)) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END + '-' + Right(@DATETEXT, 2)) END I then try to execute the following: UPDATE Credit_Card SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) WHERE LEN(Expiry_Date) = 5 --jic a bad field And get the following error: Syntax error converting character string to smalldatetime data type The function does return correctly. Can anyone give me some help in getting this working. Thanks Can you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure. This is just one of the dozens of problems with choosing the wrong data type. Another big one with your function specifically: You're checking for left(@datetext,2) but then saying WHEN 1 -- two problems here, one is that 1 is not a string ('1' would be) and unless it is november or december, I am sure that Left(@DateText, 2) yields two characters (only one of which is the month). How on earth do you distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)? Again, some sample data that causes the problem would be useful. But more importantly, before trying to debug a function, get a query running that does what you want (but without the convert to smalldatetime). That makes it much easier to debug and figure out which rows are not producing valid dates. Show quote "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616@nasal.pacific.net.au... >I want to convert a varchar(5) field to smalldatetime. I have a column that >hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived form the > column above. So I tried the following > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > RETURNS smalldatetime > AS > BEGIN > --want to be sure it is interpreted as dd-mm-yy format > RETURN CONVERT(smalldatetime, > '01-' + > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > WHEN 1 THEN 'Jan' > WHEN 2 THEN 'Feb' > WHEN 3 THEN 'Mar' > WHEN 4 THEN 'Apr' > WHEN 5 THEN 'May' > WHEN 6 THEN 'Jun' > WHEN 7 THEN 'Jul' > WHEN 8 THEN 'Aug' > WHEN 9 THEN 'Sep' > WHEN 10 THEN 'Oct' > WHEN 11 THEN 'Nov' > WHEN 12 THEN 'Dec' > END > + '-' + Right(@DATETEXT, 2)) > > END > > I then try to execute the following: > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > And get the following error: > > Syntax error converting character string to smalldatetime data type > > The function does return correctly. Can anyone give me some help in > getting this working. Thanks > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message Have a look again----CONVERT(tinyint, Left(@DATETEXT, 2)) returns 1 from news:O9Um9QblGHA.4512@TK2MSFTNGP04.phx.gbl... > Can you show a simplified example, e.g. your table structure, and 3 or 4 > rows of sample data that cause the failure. > > This is just one of the dozens of problems with choosing the wrong data > type. Another big one with your function specifically: > > You're checking for left(@datetext,2) but then saying WHEN 1 -- two > problems '01' The function DOES work correctly and returns from a parameter of '06/06' the result '2006-06-01 00:00:00' TRY the function! > here, one is that 1 is not a string ('1' would be) and unless it is READ again -- I am passing a string value with a mm/yy format e.g. '10/06'> november or december, I am sure that Left(@DateText, 2) yields two > characters (only one of which is the month). How on earth do you > distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about > 1006 (nov 06) vs. 106 (jan 06)? I am prepending '01-' for the day in the function. The reason I am converting the LEFT 2 characters to a tinyint for use in the CASE statement e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string in dd-MMM-yy format so the convert function will not be confused between an Australian date format and a US format. > AGAIN, the function works - READ what I have written. Further to this I have > Again, some sample data that causes the problem would be useful. But more > importantly, before trying to debug a function, get a query running that > does what you want (but without the convert to smalldatetime). That makes > it much easier to debug and figure out which rows are not producing valid > dates. given you sample data! "I want to convert a varchar(5) field to smalldatetime. I have a column that hold credit card expiry dates in mm/yy format e.g 10/06 I want to update a new column with a smalldatetime value derived from the column above. So I tried the following" PLEASE read and understand the question. Show quote > > > > "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message > news:qComg.3523$b6.86616@nasal.pacific.net.au... >>I want to convert a varchar(5) field to smalldatetime. I have a column >>that hold credit card expiry dates in mm/yy format e.g 10/06 >> >> I want to update a new column with a smalldatetime value derived form the >> column above. So I tried the following >> >> CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) >> RETURNS smalldatetime >> AS >> BEGIN >> --want to be sure it is interpreted as dd-mm-yy format >> RETURN CONVERT(smalldatetime, >> '01-' + >> CASE CONVERT(tinyint, Left(@DATETEXT, 2)) >> WHEN 1 THEN 'Jan' >> WHEN 2 THEN 'Feb' >> WHEN 3 THEN 'Mar' >> WHEN 4 THEN 'Apr' >> WHEN 5 THEN 'May' >> WHEN 6 THEN 'Jun' >> WHEN 7 THEN 'Jul' >> WHEN 8 THEN 'Aug' >> WHEN 9 THEN 'Sep' >> WHEN 10 THEN 'Oct' >> WHEN 11 THEN 'Nov' >> WHEN 12 THEN 'Dec' >> END >> + '-' + Right(@DATETEXT, 2)) >> >> END >> >> I then try to execute the following: >> >> UPDATE Credit_Card >> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) >> WHERE LEN(Expiry_Date) = 5 --jic a bad field >> >> And get the following error: >> >> Syntax error converting character string to smalldatetime data type >> >> The function does return correctly. Can anyone give me some help in >> getting this working. Thanks >> > > Apparently the function does NOT work -you are getting an error!
I'm confused now. Does the function NOT work properly and you are asking for our help, OR Does the function work properly and you are posting here for -what was that reason again? -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Still Love VB6" <harry@nospam.com.au> wrote in message news:Svqmg.14176$ap3.2372@news-server.bigpond.net.au... > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:O9Um9QblGHA.4512@TK2MSFTNGP04.phx.gbl... >> Can you show a simplified example, e.g. your table structure, and 3 or 4 >> rows of sample data that cause the failure. >> >> This is just one of the dozens of problems with choosing the wrong data >> type. Another big one with your function specifically: >> >> You're checking for left(@datetext,2) but then saying WHEN 1 -- two >> problems > > Have a look again----CONVERT(tinyint, Left(@DATETEXT, 2)) returns 1 from > '01' > > The function DOES work correctly and returns from a parameter of '06/06' > the result '2006-06-01 00:00:00' > > TRY the function! > >> here, one is that 1 is not a string ('1' would be) and unless it is >> november or december, I am sure that Left(@DateText, 2) yields two >> characters (only one of which is the month). How on earth do you >> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about >> 1006 (nov 06) vs. 106 (jan 06)? > > READ again -- I am passing a string value with a mm/yy format e.g. '10/06' > I am prepending '01-' for the day in the function. > The reason I am converting the LEFT 2 characters to a tinyint for use in > the CASE statement > > e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string > in dd-MMM-yy format > so the convert function will not be confused between an Australian date > format and a US format. > >> >> Again, some sample data that causes the problem would be useful. But >> more importantly, before trying to debug a function, get a query running >> that does what you want (but without the convert to smalldatetime). That >> makes it much easier to debug and figure out which rows are not producing >> valid dates. > > AGAIN, the function works - READ what I have written. Further to this I > have given you sample data! > > "I want to convert a varchar(5) field to smalldatetime. I have a column > that > hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived from the > column above. So I tried the following" > > PLEASE read and understand the question. > >> >> >> >> "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message >> news:qComg.3523$b6.86616@nasal.pacific.net.au... >>>I want to convert a varchar(5) field to smalldatetime. I have a column >>>that hold credit card expiry dates in mm/yy format e.g 10/06 >>> >>> I want to update a new column with a smalldatetime value derived form >>> the column above. So I tried the following >>> >>> CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) >>> RETURNS smalldatetime >>> AS >>> BEGIN >>> --want to be sure it is interpreted as dd-mm-yy format >>> RETURN CONVERT(smalldatetime, >>> '01-' + >>> CASE CONVERT(tinyint, Left(@DATETEXT, 2)) >>> WHEN 1 THEN 'Jan' >>> WHEN 2 THEN 'Feb' >>> WHEN 3 THEN 'Mar' >>> WHEN 4 THEN 'Apr' >>> WHEN 5 THEN 'May' >>> WHEN 6 THEN 'Jun' >>> WHEN 7 THEN 'Jul' >>> WHEN 8 THEN 'Aug' >>> WHEN 9 THEN 'Sep' >>> WHEN 10 THEN 'Oct' >>> WHEN 11 THEN 'Nov' >>> WHEN 12 THEN 'Dec' >>> END >>> + '-' + Right(@DATETEXT, 2)) >>> >>> END >>> >>> I then try to execute the following: >>> >>> UPDATE Credit_Card >>> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) >>> WHERE LEN(Expiry_Date) = 5 --jic a bad field >>> >>> And get the following error: >>> >>> Syntax error converting character string to smalldatetime data type >>> >>> The function does return correctly. Can anyone give me some help in >>> getting this working. Thanks >>> >> >> > > > Have a look again----CONVERT(tinyint, Left(@DATETEXT, 2)) returns 1 from How on earth am I supposed to know what LEFT(@DateText, 2) returns? You > '01' never showed us any sample data! I have absolutely no idea what to expect in a varchar(5) that is supposed to represent a date, but I sure can come up with some theories of why the function would fail, which is what I was trying to do. > The function DOES work correctly and returns from a parameter of '06/06' I would have, if I had ANY clue what your DATA LOOKED LIKE! Now I know, > the result '2006-06-01 00:00:00' > > TRY the function! thanks for finally showing a sample. So, if the function DOES work, have you considered ways to figure out why it doesn't work? > AGAIN, the function works - READ what I have written. Further to this I Yeah? And what about a case like January? Was I supposed to GUESS that it > have given you sample data! > > "I want to convert a varchar(5) field to smalldatetime. I have a column > that > hold credit card expiry dates in mm/yy format e.g 10/06 was 01/06? The function is failing, clearly, otherwise you wouldn't be here. Which means some of your data is BAD. I suppose we'll need to guess about that, too. > PLEASE read and understand the question. Oh, blow it out your shorts. You're not paying me for anything, and the attitude you're giving me has guaranteed that I won't try to help you again. Why don't you go read http://www.aspfaq.com/5006 so that in the future you can avoid people trying so hard to go out of their way to answer your ambiguous and incomplete question, only to get insulted for trying. Good luck. Hi, Aaron
Come down man, he is not a first and he won't be last in this news group Have a nice day. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:emKWsgelGHA.3776@TK2MSFTNGP03.phx.gbl... >> Have a look again----CONVERT(tinyint, Left(@DATETEXT, 2)) returns 1 >> from '01' > > How on earth am I supposed to know what LEFT(@DateText, 2) returns? You > never showed us any sample data! I have absolutely no idea what to expect > in a varchar(5) that is supposed to represent a date, but I sure can come > up with some theories of why the function would fail, which is what I was > trying to do. > >> The function DOES work correctly and returns from a parameter of '06/06' >> the result '2006-06-01 00:00:00' >> >> TRY the function! > > I would have, if I had ANY clue what your DATA LOOKED LIKE! Now I know, > thanks for finally showing a sample. So, if the function DOES work, have > you considered ways to figure out why it doesn't work? > >> AGAIN, the function works - READ what I have written. Further to this I >> have given you sample data! >> >> "I want to convert a varchar(5) field to smalldatetime. I have a column >> that >> hold credit card expiry dates in mm/yy format e.g 10/06 > > Yeah? And what about a case like January? Was I supposed to GUESS that > it was 01/06? The function is failing, clearly, otherwise you wouldn't be > here. Which means some of your data is BAD. I suppose we'll need to > guess about that, too. > >> PLEASE read and understand the question. > > Oh, blow it out your shorts. You're not paying me for anything, and the > attitude you're giving me has guaranteed that I won't try to help you > again. > > Why don't you go read http://www.aspfaq.com/5006 so that in the future you > can avoid people trying so hard to go out of their way to answer your > ambiguous and incomplete question, only to get insulted for trying. > > Good luck. > Harry
> --want to be sure it is interpreted as dd-mm-yy format Make sure that the format you are converting to is YYYYMMDDCREATE TABLE Credit_Card (dt VARCHAR(5)) INSERT INTO Credit_Card SELECT '10/06' SELECT * FROM Credit_Card UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01' AS DATETIME) It retruns just 'June' How about to alter the table and change the datatype's column or expand it to varchar(50) for instance Show quote "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616@nasal.pacific.net.au... >I want to convert a varchar(5) field to smalldatetime. I have a column that >hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived form the > column above. So I tried the following > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > RETURNS smalldatetime > AS > BEGIN > --want to be sure it is interpreted as dd-mm-yy format > RETURN CONVERT(smalldatetime, > '01-' + > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > WHEN 1 THEN 'Jan' > WHEN 2 THEN 'Feb' > WHEN 3 THEN 'Mar' > WHEN 4 THEN 'Apr' > WHEN 5 THEN 'May' > WHEN 6 THEN 'Jun' > WHEN 7 THEN 'Jul' > WHEN 8 THEN 'Aug' > WHEN 9 THEN 'Sep' > WHEN 10 THEN 'Oct' > WHEN 11 THEN 'Nov' > WHEN 12 THEN 'Dec' > END > + '-' + Right(@DATETEXT, 2)) > > END > > I then try to execute the following: > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > And get the following error: > > Syntax error converting character string to smalldatetime data type > > The function does return correctly. Can anyone give me some help in > getting this working. Thanks >
Show quote
"Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message Perhaps I had better explain further:news:qComg.3523$b6.86616@nasal.pacific.net.au... >I want to convert a varchar(5) field to smalldatetime. I have a column that >hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived form the > column above. So I tried the following > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > RETURNS smalldatetime > AS > BEGIN > --want to be sure it is interpreted as dd-mm-yy format > RETURN CONVERT(smalldatetime, > '01-' + > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > WHEN 1 THEN 'Jan' > WHEN 2 THEN 'Feb' > WHEN 3 THEN 'Mar' > WHEN 4 THEN 'Apr' > WHEN 5 THEN 'May' > WHEN 6 THEN 'Jun' > WHEN 7 THEN 'Jul' > WHEN 8 THEN 'Aug' > WHEN 9 THEN 'Sep' > WHEN 10 THEN 'Oct' > WHEN 11 THEN 'Nov' > WHEN 12 THEN 'Dec' > END > + '-' + Right(@DATETEXT, 2)) > > END > > I then try to execute the following: > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > And get the following error: > > Syntax error converting character string to smalldatetime data type > > The function does return correctly. Can anyone give me some help in > getting this working. Thanks I have a table with a number of columns, one of which is called "Expiry_Date" - varchar(5) which stores string values in the format mm/yy e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I now need to know in advance if a crediy card is going to expire. The current format makes that very difficult. So I am trying the following: I have added another column called "CardExpiry" which is smalldatetime. I want to update this column from values contained in the "Expiry_Date" column. Obviously I have to convert the string value to a smalldatetime value first. That is why I created the function above. Even though the function returns a smalldatetime value, sql server (2000) still thinks the output of the function is a character string. > I have added another column called "CardExpiry" which is smalldatetime. I CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)> want to update this column from values contained in the "Expiry_Date" > column. Obviously I have to convert the string value to a smalldatetime > value first. That is why I created the function above. Even though the > function returns a smalldatetime value, sql server (2000) still thinks the > output of the function is a character string. INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06' INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06' INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06' INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06' SELECT * FROM Credit_Card --Now we are going to update CardExpiry column UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT (Expiry_Date,2) +'01' AS SMALLDATETIME) SELECT * FROM Credit_Card DROP TABLE Credit_Card Show quote "Still Love VB6" <harry@nospam.com.au> wrote in message news:1Jqmg.14185$ap3.3358@news-server.bigpond.net.au... > "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message > news:qComg.3523$b6.86616@nasal.pacific.net.au... >>I want to convert a varchar(5) field to smalldatetime. I have a column >>that hold credit card expiry dates in mm/yy format e.g 10/06 >> >> I want to update a new column with a smalldatetime value derived form the >> column above. So I tried the following >> >> CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) >> RETURNS smalldatetime >> AS >> BEGIN >> --want to be sure it is interpreted as dd-mm-yy format >> RETURN CONVERT(smalldatetime, >> '01-' + >> CASE CONVERT(tinyint, Left(@DATETEXT, 2)) >> WHEN 1 THEN 'Jan' >> WHEN 2 THEN 'Feb' >> WHEN 3 THEN 'Mar' >> WHEN 4 THEN 'Apr' >> WHEN 5 THEN 'May' >> WHEN 6 THEN 'Jun' >> WHEN 7 THEN 'Jul' >> WHEN 8 THEN 'Aug' >> WHEN 9 THEN 'Sep' >> WHEN 10 THEN 'Oct' >> WHEN 11 THEN 'Nov' >> WHEN 12 THEN 'Dec' >> END >> + '-' + Right(@DATETEXT, 2)) >> >> END >> >> I then try to execute the following: >> >> UPDATE Credit_Card >> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) >> WHERE LEN(Expiry_Date) = 5 --jic a bad field >> >> And get the following error: >> >> Syntax error converting character string to smalldatetime data type >> >> The function does return correctly. Can anyone give me some help in >> getting this working. Thanks > > Perhaps I had better explain further: > > I have a table with a number of columns, one of which is called > "Expiry_Date" - varchar(5) which stores string values in the format mm/yy > e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. > I now need to know in advance if a crediy card is going to expire. The > current format makes that very difficult. So I am trying the following: > > I have added another column called "CardExpiry" which is smalldatetime. I > want to update this column from values contained in the "Expiry_Date" > column. Obviously I have to convert the string value to a smalldatetime > value first. That is why I created the function above. Even though the > function returns a smalldatetime value, sql server (2000) still thinks the > output of the function is a character string. > >
Show quote
"Uri Dimant" <u***@iscar.co.il> wrote in message Thanks Yuri. May your tribe multiply.news:edkHQWclGHA.2128@TK2MSFTNGP04.phx.gbl... >> I have added another column called "CardExpiry" which is smalldatetime. I >> want to update this column from values contained in the "Expiry_Date" >> column. Obviously I have to convert the string value to a smalldatetime >> value first. That is why I created the function above. Even though the >> function returns a smalldatetime value, sql server (2000) still thinks >> the output of the function is a character string. > > CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME) > INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06' > INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06' > INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06' > INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06' > > > SELECT * FROM Credit_Card > > --Now we are going to update CardExpiry column > > UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT > (Expiry_Date,2) +'01' AS SMALLDATETIME) > SELECT * FROM Credit_Card > > DROP TABLE Credit_Card > > > > > > "Still Love VB6" <harry@nospam.com.au> wrote in message > news:1Jqmg.14185$ap3.3358@news-server.bigpond.net.au... >> "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message >> news:qComg.3523$b6.86616@nasal.pacific.net.au... >>>I want to convert a varchar(5) field to smalldatetime. I have a column >>>that hold credit card expiry dates in mm/yy format e.g 10/06 >>> >>> I want to update a new column with a smalldatetime value derived form >>> the column above. So I tried the following >>> >>> CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) >>> RETURNS smalldatetime >>> AS >>> BEGIN >>> --want to be sure it is interpreted as dd-mm-yy format >>> RETURN CONVERT(smalldatetime, >>> '01-' + >>> CASE CONVERT(tinyint, Left(@DATETEXT, 2)) >>> WHEN 1 THEN 'Jan' >>> WHEN 2 THEN 'Feb' >>> WHEN 3 THEN 'Mar' >>> WHEN 4 THEN 'Apr' >>> WHEN 5 THEN 'May' >>> WHEN 6 THEN 'Jun' >>> WHEN 7 THEN 'Jul' >>> WHEN 8 THEN 'Aug' >>> WHEN 9 THEN 'Sep' >>> WHEN 10 THEN 'Oct' >>> WHEN 11 THEN 'Nov' >>> WHEN 12 THEN 'Dec' >>> END >>> + '-' + Right(@DATETEXT, 2)) >>> >>> END >>> >>> I then try to execute the following: >>> >>> UPDATE Credit_Card >>> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) >>> WHERE LEN(Expiry_Date) = 5 --jic a bad field >>> >>> And get the following error: >>> >>> Syntax error converting character string to smalldatetime data type >>> >>> The function does return correctly. Can anyone give me some help in >>> getting this working. Thanks >> >> Perhaps I had better explain further: >> >> I have a table with a number of columns, one of which is called >> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy >> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. >> I now need to know in advance if a crediy card is going to expire. The >> current format makes that very difficult. So I am trying the following: >> >> I have added another column called "CardExpiry" which is smalldatetime. I >> want to update this column from values contained in the "Expiry_Date" >> column. Obviously I have to convert the string value to a smalldatetime >> value first. That is why I created the function above. Even though the >> function returns a smalldatetime value, sql server (2000) still thinks >> the output of the function is a character string. >> >> > Could it be that you have bad data in your table, for example an expiry date
of 13/06? Chris Show quote "Still Love VB6" wrote: > "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message > news:qComg.3523$b6.86616@nasal.pacific.net.au... > >I want to convert a varchar(5) field to smalldatetime. I have a column that > >hold credit card expiry dates in mm/yy format e.g 10/06 > > > > I want to update a new column with a smalldatetime value derived form the > > column above. So I tried the following > > > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > > RETURNS smalldatetime > > AS > > BEGIN > > --want to be sure it is interpreted as dd-mm-yy format > > RETURN CONVERT(smalldatetime, > > '01-' + > > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > > WHEN 1 THEN 'Jan' > > WHEN 2 THEN 'Feb' > > WHEN 3 THEN 'Mar' > > WHEN 4 THEN 'Apr' > > WHEN 5 THEN 'May' > > WHEN 6 THEN 'Jun' > > WHEN 7 THEN 'Jul' > > WHEN 8 THEN 'Aug' > > WHEN 9 THEN 'Sep' > > WHEN 10 THEN 'Oct' > > WHEN 11 THEN 'Nov' > > WHEN 12 THEN 'Dec' > > END > > + '-' + Right(@DATETEXT, 2)) > > > > END > > > > I then try to execute the following: > > > > UPDATE Credit_Card > > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > > > And get the following error: > > > > Syntax error converting character string to smalldatetime data type > > > > The function does return correctly. Can anyone give me some help in > > getting this working. Thanks > > Perhaps I had better explain further: > > I have a table with a number of columns, one of which is called > "Expiry_Date" - varchar(5) which stores string values in the format mm/yy > e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I > now need to know in advance if a crediy card is going to expire. The current > format makes that very difficult. So I am trying the following: > > I have added another column called "CardExpiry" which is smalldatetime. I > want to update this column from values contained in the "Expiry_Date" > column. Obviously I have to convert the string value to a smalldatetime > value first. That is why I created the function above. Even though the > function returns a smalldatetime value, sql server (2000) still thinks the > output of the function is a character string. > > > > Perhaps I had better explain further: Yes, that would be a good start!> Even though the function returns a smalldatetime value, sql server (2000) No, that is not what is happening at all.> still thinks the output of the function is a character string. You have some "dates" in your table where they aren't really dates. I can think of hundreds of examples, since you allow varchar(5) in there, there is no easy way to make them conform to any date format, so your table is probably full of crap. It may be one row that is causing your function to fail; it may be all rows! Who knows? Do you see, now, the importance of sample data!?
Show quote
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message Well, you have certainly shown me the importance of avoiding self news:ewirSielGHA.3304@TK2MSFTNGP03.phx.gbl... >> Perhaps I had better explain further: > > Yes, that would be a good start! > >> Even though the function returns a smalldatetime value, sql server (2000) >> still thinks the output of the function is a character string. > > No, that is not what is happening at all. > > You have some "dates" in your table where they aren't really dates. I can > think of hundreds of examples, since you allow varchar(5) in there, there > is no easy way to make them conform to any date format, so your table is > probably full of crap. It may be one row that is causing your function to > fail; it may be all rows! Who knows? > > Do you see, now, the importance of sample data!? enhancement when trying to help others. Where do you get one of those MVP things anyway, a raffle at the local county fair? Throw away self enhancing titles, Aaron. How you behave towards other is the only title you need. A little kindness and understanding is the way forward, not bombastic lashings and patronisation. :) > not bombastic lashings What, like "read and understand the question"?*PLONK* > not bombastic lashings What, like "read and understand the question"?*PLONK* > Perhaps I had better explain further: Yes, that would be a good start!> Even though the function returns a smalldatetime value, sql server (2000) No, that is not what is happening at all.> still thinks the output of the function is a character string. You have some "dates" in your table where they aren't really dates. I can think of hundreds of examples, since you allow varchar(5) in there, there is no easy way to make them conform to any date format, so your table is probably full of crap. It may be one row that is causing your function to fail; it may be all rows! Who knows? Do you see, now, the importance of sample data!? This alteration to your function shuold clear up the error you are receiving.
It returns a alpha month, 4 digit year, and the first of the month. Bad and non-confirming parameters will return NULL. You can then easily find the bad data. CREATE FUNCTION dbo.StringToDate ( @DateText varchar(11) ) RETURNS datetime AS BEGIN IF len( @DateText ) < 5 RETURN NULL SET @DateText = CASE left( @DateText, 2 ) WHEN '01' THEN replace( @DateText, '01/', '01/Jan/' ) WHEN '02' THEN replace( @DateText, '02/', '01/Feb/' ) WHEN '03' THEN replace( @DateText, '03/', '01/Mar/' ) WHEN '04' THEN replace( @DateText, '04/', '01/Apr/' ) WHEN '05' THEN replace( @DateText, '05/', '01/May/' ) WHEN '06' THEN replace( @DateText, '06/', '01/Jun/' ) WHEN '07' THEN replace( @DateText, '07/', '01/Jul/' ) WHEN '08' THEN replace( @DateText, '08/', '01/Aug/' ) WHEN '09' THEN replace( @DateText, '09/', '01/Sep/' ) WHEN '10' THEN replace( @DateText, '10/', '01/Oct/' ) WHEN '11' THEN replace( @DateText, '11/', '01/Nov/' ) WHEN '12' THEN replace( @DateText, '12/', '01/Dec/' ) END IF @DateText LIKE '%/0%' RETURN ( replace( @DateText, '/0', '/200' )) IF @DateText LIKE '%/1%' RETURN ( replace( @DateText, '/1', '/201' )) RETURN NULL END -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616@nasal.pacific.net.au... >I want to convert a varchar(5) field to smalldatetime. I have a column that > hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived form the > column above. So I tried the following > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > RETURNS smalldatetime > AS > BEGIN > --want to be sure it is interpreted as dd-mm-yy format > RETURN CONVERT(smalldatetime, > '01-' + > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > WHEN 1 THEN 'Jan' > WHEN 2 THEN 'Feb' > WHEN 3 THEN 'Mar' > WHEN 4 THEN 'Apr' > WHEN 5 THEN 'May' > WHEN 6 THEN 'Jun' > WHEN 7 THEN 'Jul' > WHEN 8 THEN 'Aug' > WHEN 9 THEN 'Sep' > WHEN 10 THEN 'Oct' > WHEN 11 THEN 'Nov' > WHEN 12 THEN 'Dec' > END > + '-' + Right(@DATETEXT, 2)) > > END > > I then try to execute the following: > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > And get the following error: > > Syntax error converting character string to smalldatetime data type > > The function does return correctly. Can anyone give me some help in getting > this working. Thanks > > Thank you for your excellent response. There was actually one bad record (0_/08) in 118590 records, hence the error . I am still mystified how that got there with the data validation routines we have in place. Good old Murphy. A slight alteration to my function based on your suggestion and voila...problem solved. Good stuff. Thanks again
"Arnie Rowland" <ar***@1568.com> wrote in message news:On0HulclGHA.4100@TK2MSFTNGP05.phx.gbl... This alteration to your function shuold clear up the error you are receiving. It returns a alpha month, 4 digit year, and the first of the month. Bad and non-confirming parameters will return NULL. You can then easily find the bad data. CREATE FUNCTION dbo.StringToDate ( @DateText varchar(11) ) RETURNS datetime AS BEGIN IF len( @DateText ) < 5 RETURN NULL SET @DateText = CASE left( @DateText, 2 ) WHEN '01' THEN replace( @DateText, '01/', '01/Jan/' ) WHEN '02' THEN replace( @DateText, '02/', '01/Feb/' ) WHEN '03' THEN replace( @DateText, '03/', '01/Mar/' ) WHEN '04' THEN replace( @DateText, '04/', '01/Apr/' ) WHEN '05' THEN replace( @DateText, '05/', '01/May/' ) WHEN '06' THEN replace( @DateText, '06/', '01/Jun/' ) WHEN '07' THEN replace( @DateText, '07/', '01/Jul/' ) WHEN '08' THEN replace( @DateText, '08/', '01/Aug/' ) WHEN '09' THEN replace( @DateText, '09/', '01/Sep/' ) WHEN '10' THEN replace( @DateText, '10/', '01/Oct/' ) WHEN '11' THEN replace( @DateText, '11/', '01/Nov/' ) WHEN '12' THEN replace( @DateText, '12/', '01/Dec/' ) END IF @DateText LIKE '%/0%' RETURN ( replace( @DateText, '/0', '/200' )) IF @DateText LIKE '%/1%' RETURN ( replace( @DateText, '/1', '/201' )) RETURN NULL END -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam Show quote "Harry Strybos" <harry_NOSPAM@ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616@nasal.pacific.net.au... >I want to convert a varchar(5) field to smalldatetime. I have a column that > hold credit card expiry dates in mm/yy format e.g 10/06 > > I want to update a new column with a smalldatetime value derived form the > column above. So I tried the following > > CREATE FUNCTION [dbo].[StringToDate] (@DATETEXT varchar(5)) > RETURNS smalldatetime > AS > BEGIN > --want to be sure it is interpreted as dd-mm-yy format > RETURN CONVERT(smalldatetime, > '01-' + > CASE CONVERT(tinyint, Left(@DATETEXT, 2)) > WHEN 1 THEN 'Jan' > WHEN 2 THEN 'Feb' > WHEN 3 THEN 'Mar' > WHEN 4 THEN 'Apr' > WHEN 5 THEN 'May' > WHEN 6 THEN 'Jun' > WHEN 7 THEN 'Jul' > WHEN 8 THEN 'Aug' > WHEN 9 THEN 'Sep' > WHEN 10 THEN 'Oct' > WHEN 11 THEN 'Nov' > WHEN 12 THEN 'Dec' > END > + '-' + Right(@DATETEXT, 2)) > > END > > I then try to execute the following: > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date) > WHERE LEN(Expiry_Date) = 5 --jic a bad field > > And get the following error: > > Syntax error converting character string to smalldatetime data type > > The function does return correctly. Can anyone give me some help in getting > this working. Thanks > > For Access Datbase,
UPDATE Credit_Card SET Credit_Card.CardExpiry = CDate(('01-' & format(left(Expiry_Date,2),'mmm') & '-' & right(Expiry_Date,2))) WHERE LEN(Expiry_Date) = 5 Thanks Veena *** Sent via Developersdex http://www.developersdex.com ***
Show quote
"Veena MG" <veena***@yahoo.com> wrote in message Thanks Veena. Another polite and non self enhancing response. Cheersnews:%23PnH5xelGHA.4132@TK2MSFTNGP05.phx.gbl... > > For Access Datbase, > > UPDATE Credit_Card > SET Credit_Card.CardExpiry = CDate(('01-' > & format(left(Expiry_Date,2),'mmm') & '-' > & right(Expiry_Date,2))) > WHERE LEN(Expiry_Date) = 5 > > Thanks > Veena > > > *** Sent via Developersdex http://www.developersdex.com ***  |
|||||||||||||||||||||||
Other interesting topics