First Date in the Month

I have a question about creating view for these coloum and have a lot trouble, before that I describe my tables :

Table A :
Code as nvarchar
Ammount1 as money
Date as datetime -> transaction date
primary key are Code and Date1

Table B :
Code as nvarchar
Ammount2 as money
Date as datetime -> transaction date
primary key are Code and Date2

I like joining this table and create table/view that result :

Code | Amount1 Today | Amount2 Today | Amount1 Last Year | Amount1 Month to date (Sum from first day month till today) | Amount2 month to date | Amount1 Last Year month to date

The parameter only for @.Date.

First I have problem to get queries specially for first date in the month, I used CAST but it doesn't work, then I used CONVERT and CAST still doesn't work.

Anybody help me ?
Thx a lot.
ad1k4r4first date of the month for getDate...

select cast('01/' + '0'+cast(month(getDate()) as varchar) + '/' + cast(year(getDate()) as varchar) as datetime)

replace getDate with @.Date and you should be right... I think...|||I like this method because it is more concise:

select convert(char(7), getdate(), 120) + '-01'|||hehehe I knew there must be a better way,... that's why I'm not a db developer. ;)

Is there a way to do it with a 3 character month?? I perfer 3 char month names because then you don't get any confusion with date time settings etc...|||I'm sure there is. Look up the formatting parameters for the CONVERT function. You can use the technique of converting the result to a shortened CHAR value whenever the results of the CONVERT function are consistent in the length of their output.

I understand you desire to use three character month names to make the result more easily readable by carbon based processors, but formatting style "120" is more universally recognized by silicon based processors. Personally, I would refer any more complex formatting to the user interface.

The other big advantage of style "120" is that the result: YYYY-MM-DD sorts correctly even as a string.|||actually the reason I like the 3 char month is because you can never rely on people to set up servers or accounts with the correct language settings... and depending on what settings you have 01-04-2004 could be recorded in the system as 1 April or 4 Jan where as with either set up 1-Apr-2004 is always 1 April.

The database will then hold the value in whatever format it likes and reformating it on the way out is very much the role of whatever interface you want to implement. :)|||Ok, I'd start with:CREATE PROCEDURE dbo.patp
@.pdAsof DATETIME
AS

DECLARE @.dFirst DATETIME -- First of the month

SELECT @.dFirst = Convert(CHAR(8), @.pdAsOf, 121) + '01'

SELECT
Coalesce(a.code, b.code) AS code
, a.amount1
, b.amount2
, (SELECT Sum(c.amount1)
FROM tableA AS c
WHERE c.code = Coalesce(a.code, b.code)
AND c.[date] = DateAdd(year, -1, Coalesce(a.[date], b.[date])) AS amount1_last_year
, (SELECT Sum(d.amount1)
FROM tableA AS d
WHERE d.code = Coalesce(a.code, b.code)
AND d.date BETWEEN @.dFirst AND
Coalesce(a.[date], b.[date])) AS amount1_mtd
FROM tableA AS a
FULL OUTER JOIN tableB AS b
ON (b.code = a.code
AND b.[date] = a.[date])

RETURNThat's only part of the solution, but let's see if that part does what you want before we get too crazy!

-PatP