|
|
You're in the
sql server programming
group:Convert text to MM/YY date in Microsoft SQL
sql server programming:
processing where the date is stored in a text column in the format MM/YY. The credit card processing software will not accept it any other way, there must always be two digits for the months and two for the year. Instead of storing the date as a datetime and pulling the relevant "datepart" the programmer choose to use a text column. The issue I have now is that I need to create a view where the accounts that have cards that will expire in the next 2 months are displayed. Can someone give me an example of a MS SQL select that will convert the text value of "MM/YY" to a datetime data type that can be used in date functions and in the format MM/YY so the CC software does not puke. I do have the option to insert the date as a datetime in a new column and delete the old text column, but I can not get it formated so the datetime column will accept the data from the text column. or alternativley A select that will find dates in the text filed where the right two characters are less than the year would be in two months from now and the left two characters are less than the month would be in two months from now. Any suggestions?
Assuming your data is always in the form mm/yy with mm and
yy always being two characters convert(datetime,left(vchMMYY,2) + '/01/' + right (vchMMYY,2)) will give you the first of each month for the expiration date. You could then use datediff to retrieve the values you want. For example: create table #test (vchMMYY nchar(5)) insert into #test values ('10/03') insert into #test values ('11/03') insert into #test values ('12/03') insert into #test values ('01/03') insert into #test values ('10/04') insert into #test values ('11/04') select * from #test where datediff(mm,getdate(),convert(datetime,left (vchMMYY,2) + '/01/' + right(vchMMYY,2))) between 1 and 2 drop table #test [quoted text, click to view] >-----Original Message-----
>I have a table that is used primarily for credit card transaction >processing where the date is stored in a text column in the format >MM/YY. The credit card processing software will not accept it any >other way, there must always be two digits for the months and two for >the year. > >Instead of storing the date as a datetime and pulling the relevant >"datepart" the programmer choose to use a text column. > >The issue I have now is that I need to create a view where the >accounts that have cards that will expire in the next 2 months are >displayed. > >Can someone give me an example of a MS SQL select that will convert >the text value of "MM/YY" to a datetime data type that can be used in >date functions and in the format MM/YY so the CC software does not >puke. > >I do have the option to insert the date as a datetime in a new column >and delete the old text column, but I can not get it formated so the >datetime column will accept the data from the text column. > >or alternativley > >A select that will find dates in the text filed where the right two >characters are less than the year would be in two months from now and >the left two characters are less than the month would be in two months >from now. > >Any suggestions? > >Thanks. >.
Greetings Damon, To Retrieve a Date column as Varchar, in MM/YY Format, use: SUBSTRING (Convert( varchar(10), <DateCol>, 103 ), 4, 10) Ex. SUBSTRING (Convert( varchar(10), GetDate(), 103 ), 4, 10) To convert a text field of MM\YY format into Date, use: CAST ( '01/'+<DateCol> As DateTime) Ex. SELECT Cast ( '01/'+ '09/02' As DateTime ) Regards, Anand. Originally posted by Damon [quoted text, click to view] > I have a table that is used primarily for credit card transaction > processing where the date is stored in a text column in the format > MM/YY. The credit card processing software will not accept it any > other way, there must always be two digits for the months and two for > the year. > > Instead of storing the date as a datetime and pulling the relevant > "datepart" the programmer choose to use a text column. > > The issue I have now is that I need to create a view where the > accounts that have cards that will expire in the next 2 months are > displayed. > > Can someone give me an example of a MS SQL select that will convert > the text value of "MM/YY" to a datetime data type that can be used in > date functions and in the format MM/YY so the CC software does not > puke. > > I do have the option to insert the date as a datetime in a new column > and delete the old text column, but I can not get it formated so the > datetime column will accept the data from the text column. > > or alternativley > > A select that will find dates in the text filed where the right two > characters are less than the year would be in two months from now and > the left two characters are less than the month would be in two months > from now. > > Any suggestions? > Thanks. --
nicely done, this worked flawlessly (changed the between to 0 and 1 to
get dates in the current month or next month). I hope you are well compensated for your skills! Thank you, Damon [quoted text, click to view] "John" <john.elenbaas@metavante.com> wrote in message news:<0a7501c3986e$159a8ae0$a101280a@phx.gbl>...
> Assuming your data is always in the form mm/yy with mm and > yy always being two characters > > convert(datetime,left(vchMMYY,2) + '/01/' + right > (vchMMYY,2)) > > will give you the first of each month for the expiration > date. You could then use datediff to retrieve the values > you want. > > For example: > create table #test (vchMMYY nchar(5)) > > insert into #test values ('10/03') > insert into #test values ('11/03') > insert into #test values ('12/03') > insert into #test values ('01/03') > insert into #test values ('10/04') > insert into #test values ('11/04') > > select * > from #test > where datediff(mm,getdate(),convert(datetime,left > (vchMMYY,2) + '/01/' + right(vchMMYY,2))) between 1 and 2 > drop table #test > > > >-----Original Message----- > >I have a table that is used primarily for credit card > transaction > >processing where the date is stored in a text column in > the format > >MM/YY. The credit card processing software will not > accept it any > >other way, there must always be two digits for the months > and two for > >the year. > > > >Instead of storing the date as a datetime and pulling the > relevant > >"datepart" the programmer choose to use a text column. > > > >The issue I have now is that I need to create a view > where the > >accounts that have cards that will expire in the next 2 > months are > >displayed. > > > >Can someone give me an example of a MS SQL select that > will convert > >the text value of "MM/YY" to a datetime data type that > can be used in > >date functions and in the format MM/YY so the CC software > does not > >puke. > > > >I do have the option to insert the date as a datetime in > a new column > >and delete the old text column, but I can not get it > formated so the > >datetime column will accept the data from the text column. > > > >or alternativley > > > >A select that will find dates in the text filed where the > right two > >characters are less than the year would be in two months > from now and > >the left two characters are less than the month would be > in two months > >from now. > > > >Any suggestions? > > > >Thanks. > >.
Aren't missing information here? You don't have year, so how can we calculate month spans? (Think
leapyears.) -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "Damon" <damon@soho-systems.com> wrote in message news:376854fe.0310212247.411e9c59@posting.google.com... > I have a table that is used primarily for credit card transaction > processing where the date is stored in a text column in the format > MM/YY. The credit card processing software will not accept it any > other way, there must always be two digits for the months and two for > the year. > > Instead of storing the date as a datetime and pulling the relevant > "datepart" the programmer choose to use a text column. > > The issue I have now is that I need to create a view where the > accounts that have cards that will expire in the next 2 months are > displayed. > > Can someone give me an example of a MS SQL select that will convert > the text value of "MM/YY" to a datetime data type that can be used in > date functions and in the format MM/YY so the CC software does not > puke. > > I do have the option to insert the date as a datetime in a new column > and delete the old text column, but I can not get it formated so the > datetime column will accept the data from the text column. > > or alternativley > > A select that will find dates in the text filed where the right two > characters are less than the year would be in two months from now and > the left two characters are less than the month would be in two months > from now. > > Any suggestions? > > Thanks.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |