Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jaacmmason
Starting Member
4 Posts |
 Posted - 2013-03-12 : 09:19:49
|
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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-12 : 12:20:15
|
| 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. |
 |
|
|
jaacmmason
Starting Member
4 Posts |
Posted - 2013-03-20 : 10:59:48
|
| 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" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-20 : 11:32:26
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
jaacmmason
Starting Member
4 Posts |
Posted - 2013-03-20 : 12:06:38
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-20 : 13:04:45
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
jaacmmason
Starting Member
4 Posts |
Posted - 2013-03-21 : 16:22:21
|
| Thank you! I will try your suggestion on the correct area. |
|
|
|
|
|
|
|
|
Help to convert String to Int for math, then back