Get year and month from sql server using foxpro9

[Niki_S]

I have a foxpro code as below.

stra="SELECT cSggNo, cInvNo, cProdFty , cGMTName, cHTSCode ,SUM(nInvQty) as nInvQty,dXFactory,cGrnNumber  from MIS.dbo.vInvFinal where dXFactory >= ?thisform.txtFromDate.Value AND dXFactory <= ?thisform.txtToDate.Value "
stra=stra+" AND cSggNo IS NOT NULL and cGrnNumber IS NOT NULL group by cSggNo,cInvNo,cProdFty,cGMTName,cHTSCode,dXFactory,cGrnNumber  "
SQLEXEC(hndOps,stra,'SGG_GRN')

_YearMonth=**here i ned to create a variable to get month and year form [b]dXFactory[/b]***

If the dXFactory= 2010-01-08 00:00:00.000 I need to get my output as 2010 January
Can anyone please help me to get this?
Thank you

 

[Mike Lewis]

Something like the following:

SELECT CAST(Year(dXFactory) AS Char) + ' ' + DATENAME(Month,  dXFactory) AS YearMonth FROM ... etc

Not tested, but I think it's correct.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the way, this isn't really a VFP question. Since the question is concerned entirely with T-SQL syntax, you might be better asking it in the SQL Server forum. The fact that you are using SQLEXEC() to send the query from VFP is not relevant in this case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

SELECT CAST(Year(SGG_GRN.dXFactory) AS Char) + ' ' + DATENAME(Month,SGG_GRN.dXFactory) AS YearMonth FROM SGGGRNs_DATE INTO CURSOR DateY

I used this code but thisis not working. Why is that?

 

[Mike Lewis]

Because you are doing it in VFP. The code I gave you was for T-SQL. I intended that you incorporate it into the SELECT statement that you are sending to SQL Server.

The VFP equivalent would be:

SELECT TRANSFORM(YEAR(dXFactory)) + ' ' + CMONTH(dXFactory) AS YearMonth ;
  FROM SGGGRNs_DATE INTO CURSOR DateY

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Why so complicated?

You get a VFP datetime from an MSSQL datetime and can use the VFP functions on that as follows:

TEXT TO stra NOSHOW
SELECT cSggNo, cInvNo, cProdFty , cGMTName, cHTSCode ,SUM(nInvQty) as nInvQty,dXFactory,cGrnNumber from MIS.dbo.vInvFinalM
where [highlight #FCE94F]dXFactory >= ?thisform.txtFromDate.Value AND dXFactory <= ?thisform.txtToDate.Value[/highlight]
AND cSggNo IS NOT NULL and cGrnNumber IS NOT NULL group by cSggNo,cInvNo,cProdFty,cGMTName,cHTSCode,dXFactory,cGrnNumber
ENDTTEXT

SQLEXEC(hndOps,stra,'SGG_GRN')

First of all, the where clause can bring multiple records depending on how long your date range from .txtFromDate.Value to .txtToDate.Value is.
But let's concentrate on dxFactory of each single record you get back:

TEXT TO stra NOSHOW
SELECT cSggNo, cInvNo, cProdFty , cGMTName, cHTSCode ,SUM(nInvQty) as nInvQty,dXFactory,cGrnNumber from MIS.dbo.vInvFinalM
where [highlight #FCE94F]dXFactory >= ?thisform.txtFromDate.Value AND dXFactory <= ?thisform.txtToDate.Value[/highlight]
AND cSggNo IS NOT NULL and cGrnNumber IS NOT NULL group by cSggNo,cInvNo,cProdFty,cGMTName,cHTSCode,dXFactory,cGrnNumber
ENDTTEXT

SQLEXEC(hndOps,stra,'SGG_GRN')

[highlight #FCE94F]Select SGG_GRN
Scan
    ? 'date(time):', dXFactory, 'year:', Year(dxFactory), 'month:' Month(dxFactory), 'day:', Day(dXFactory)
Endscan[/highlight]

So in short you can use VFPs YEAR(), MOTNH(), and DAY() functions, no need to use them within MSSQL and create a string that you then turn back into the dateparts in VFP again. Just get the date(time) and work on it. Always stay with the most specific datetypes to be able to use date type specific commands and functions.


Chriss

 

[Niki_S]

SELECT TRANSFORM(YEAR(dXFactory)) + ' ' + CMONTH(dXFactory) AS YearMonth ;
  FROM SGG_GRN INTO CURSOR SGG_GRN_Y

I used above code to get Year and Month. But now I want to get it through my below select statement.

stra="SELECT cSggNo, cInvNo, cProdFty , cGMTName, cHTSCode ,SUM(nInvQty) as nInvQty,[b]dXFactory[/b],cGrnNumber  from MIS.dbo.vInvFinal where dXFactory >= ?thisform.txtFromDate.Value AND dXFactory <= ?thisform.txtToDate.Value "
stra=stra+" AND cSggNo IS NOT NULL and cGrnNumber IS NOT NULL group by cSggNo,cInvNo,cProdFty,cGMTName,cHTSCode,dXFactory,cGrnNumber  "
SQLEXEC(hndOps,stra,'SGG_GRN')

In my above code there have a field named dXFactory. I need to get Year and Month from this inside my above select statement.
Here I need to have two fields as dXFactory and YearMonth
Can anyone please help me?
Thank You

 

[Mike Lewis]

Please read what I wrote above.

The first code I gave you (using CAST(), etc.) was for SQL Server. You add it to the SELECT statement that you are sending to the server. But then you said it didn't work, and you showed something completely different: a VFP SELECT ... INTO CURSOR statement. So I gave you some code that would work in VFP.

Now you say you want to get the result into the original SELECT statement after all - the one you are sending to SQL Server.

It doesn't help when you send us round in circles like this. And it doesn't help if you simply say something doesn't work. We have to know why it didn't work - that is, what went wrong.

Please try to understand the information we are giving you and how you can use it to solve your problem. If something doesn't work, tell us what went wrong. Don't make it so difficult for us to help you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads