SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help to convert String to Int for math, then back
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaacmmason
Starting Member

USA
4 Posts

Posted - 03/12/2013 :  09:19:49  Show Profile  Reply with Quote
I know I cannot mathmatically subtract 1 from a string value, but I also do not know how to truly convert a string to integer, do my subtraction, then convert back to a string so I can continue using the string value. So, this is what I have:
Dim cyclesQuery As String = "SELECT id, CONCAT(DATE_FORMAT(start, '%m/%d'), ' to ', DATE_FORMAT(end, '%m/%d')) AS duration, ROUND(DATEDIFF(end, start) / 7) AS weeks FROM cycles WHERE YEAR(start) = ?yid ORDER BY id"
To populate the yid field, then used as below to gather the correct year of data.
Dim searchQuery As String = Request.QueryString("yid") & "-" & String.Format("{0:d2}", i) & "-" & "01"
The format has changed from gathering Jan - December data of same year to now needing to gather October of one year to September of the following year. Any suggestions to assist me in this would be helpful.

The currect value of yid is 2013. I need it to be 2012 within a loop (3 occurances) and then convert back to string so I can continue using it, of course it will need a different name once it is converted.

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/12/2013 :  12:20:15  Show Profile  Reply with Quote
Are you trying to do the conversion in SQL or VB?

In SQL there are several fucntions that may help. The first are CAST and CONVERT. Those can convert between differetn datatypes. If you have actual DATE values, then there are other functions that may help like YEAR and MONTH.

Edited by - Lamprey on 03/12/2013 12:21:34
Go to Top of Page

jaacmmason
Starting Member

USA
4 Posts

Posted - 03/20/2013 :  10:59:48  Show Profile  Reply with Quote
From what I can tell this is a Date field. I am only grabbing the year out of it and using this year to display data. The requirments of the data have changed, and as I am newer to SQL and VBScript, I sm not sure what to look for. I have tried to use DateAdd within the SQL Select statement. I get error messages based on with the DateAdd does not exist, or expected end of line. I do not know what I search for online to try and determine how to successfully modify this piece of code to show October of one year to September of the following year.

As far as the DateAdd - it looked like this:
Dim cyclesQuery As String = "SELECT id, CONCAT(DATE_FORMAT(start, '%m/%d'), ' to ', DATE_FORMAT(end, '%m/%d')) AS duration, ROUND(DATEDIFF(end, start) / 7) AS weeks FROM cycles WHERE YEAR(start) = DataAdd('yyyy', -1, ?yid) ORDER BY id"

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/20/2013 :  11:32:26  Show Profile  Reply with Quote
is your backend SQL Server or Access? CONCAT works in Access but in SQL its available only from 2012 onwards. Also DATEADD,DATEDIFF require three arguments where first argument is a interval (d for day,m for month etc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jaacmmason
Starting Member

USA
4 Posts

Posted - 03/20/2013 :  12:06:38  Show Profile  Reply with Quote
Back end Is MySQL.

Thank you for the information. I did not see this function. I will play around and see what I can accomplish with this.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/20/2013 :  13:04:45  Show Profile  Reply with Quote
then you should be posting this in MySQL forums. All above used functions are t-sql access based so they may not be available in MySQL. Also this is MS SQL Server forum so we dont have much expertise in MySQL. try your luck at www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jaacmmason
Starting Member

USA
4 Posts

Posted - 03/21/2013 :  16:22:21  Show Profile  Reply with Quote
Thank you! I will try your suggestion on the correct area.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000