June 18, 2003 at 11:49 am
I have a view set up for a daily import of invoices. Somewhere in my view, I need to indicate that that the date should be a short date (I think) before I use GROUP BY to insure all items on a single invoice get grouped, even if they were invoiced 1/10 of a second apart. Or is there a better way to do this?
"The grass is always greener over the septic tank." ~Leaf
June 18, 2003 at 12:17 pm
USE:
CONVERT(DATETIME, DATEDIFF(DAY, 0, GETDATE()))
to strip the time portion of the date.
Edited by - jpipes on 06/18/2003 12:20:51 PM
June 18, 2003 at 12:22 pm
here's just a different variation:
SELECT DATEADD(d, 0, DATEDIFF(DAY, 0, GETDATE()))
June 18, 2003 at 12:39 pm
Perhaps I was not clear, let me post the code.
CREATE VIEW dbo.vwImport
AS
SELECT dbo.tblSHOrderItems.dteShipped, dbo.tblSHOrderItems.txtInvNum, dbo.tblSHOrderItems.txtSuffix, dbo.tblSHOrdHead.txtCustomerPO,
SUM(dbo.tblSHOrderItems.intQuantity * dbo.tblSHOrderItems.curPrice) AS subtotal, dbo.tblSHOrdHead.curTaxAmt, dbo.tblSHOrdHead.pk,
SUM(dbo.tblSHOrderItems.intQuantity * dbo.tblSHOrderItems.curPrice) + dbo.tblSHOrdHead.curTaxAmt + dbo.tblSHOrdHead.curFreight AS grandtotal,
dbo.tblSHOrdHead.curFreight, dbo.tblSHOrdHead.txtCustnum
FROM dbo.tblSHOrderItems INNER JOIN
dbo.tblSHOrdHead ON dbo.tblSHOrderItems.FKOrdHead = dbo.tblSHOrdHead.pk
GROUP BY dbo.tblSHOrderItems.dteShipped, dbo.tblSHOrderItems.txtInvNum, dbo.tblSHOrderItems.txtSuffix, dbo.tblSHOrdHead.txtCustomerPO,
dbo.tblSHOrdHead.pk, dbo.tblSHOrdHead.curTaxAmt, dbo.tblSHOrdHead.curFreight, dbo.tblSHOrdHead.txtCustnum
I need to extract a date that is there without the time indication so all items on an invoice are grouped together.
"The grass is always greener over the septic tank." ~Leaf
June 18, 2003 at 12:42 pm
Like I said, strip the time:
CREATE VIEW dbo.vwImport
AS
SELECT DATEADD(d, 0, DATEDIFF(DAY, 0, dbo.tblSHOrderItems.dteShipped)) AS dteShipped, dbo.tblSHOrderItems.txtInvNum, dbo.tblSHOrderItems.txtSuffix, dbo.tblSHOrdHead.txtCustomerPO,
SUM(dbo.tblSHOrderItems.intQuantity * dbo.tblSHOrderItems.curPrice) AS subtotal, dbo.tblSHOrdHead.curTaxAmt, dbo.tblSHOrdHead.pk,
SUM(dbo.tblSHOrderItems.intQuantity * dbo.tblSHOrderItems.curPrice) + dbo.tblSHOrdHead.curTaxAmt + dbo.tblSHOrdHead.curFreight AS grandtotal,
dbo.tblSHOrdHead.curFreight, dbo.tblSHOrdHead.txtCustnum
FROM dbo.tblSHOrderItems
INNER JOIN dbo.tblSHOrdHead
ON dbo.tblSHOrderItems.FKOrdHead = dbo.tblSHOrdHead.pk
GROUP BY DATEADD(d, 0, DATEDIFF(DAY, 0, dbo.tblSHOrderItems.dteShipped)), dbo.tblSHOrderItems.txtInvNum, dbo.tblSHOrderItems.txtSuffix, dbo.tblSHOrdHead.txtCustomerPO,
dbo.tblSHOrdHead.pk, dbo.tblSHOrdHead.curTaxAmt, dbo.tblSHOrdHead.curFreight, dbo.tblSHOrdHead.txtCustnum
June 18, 2003 at 12:49 pm
Sorry, I didn't realize what the DATEDIFF was for. Thank you.
"The grass is always greener over the septic tank." ~Leaf
June 19, 2003 at 3:24 am
another way is just to do a select
left(getdate(),12) - does return it as a varchar though
June 19, 2003 at 6:38 am
quote:
another way is just to do a selectleft(getdate(),12) - does return it as a varchar though
This is true, but might not work with all localized date formats, so check and be careful...
June 19, 2003 at 8:35 am
Another way to do it is:
Convert(char(8),dteShipped,1)
This will convert the date to a mm/dd/yy format.
June 19, 2003 at 8:40 am
but char(8) will not work if I'm using a mm/dd/yyyy format.
"The grass is always greener over the septic tank." ~Leaf
June 19, 2003 at 8:45 am
If you want to convert it to a mm/dd/yyyy format then use:
Convert(char(10),dteShipped,101)
This will take the datetime field dteShipped and convert it to a char(10) field in style format 101 which is mm/dd/yyyy.
June 19, 2003 at 2:44 pm
If you're not working with a datetime field you'll have to cast as datetime first.
varchar output: Convert(varchar,cast(dteShipped as datetime),101)
datetime output: cast(Convert(varchar,cast(dteShipped as datetime),101) as datetime)
Signature is NULL
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply