Need first day of previous month for criteria in a SQL view.

[ljevans]

When this view is run, all of the discrepancies written the previous month are displayed. I've got the last day of the previous month, but I can't get the first day. Any help would be appreciated.

A criteria example: Between 10/1/2006 and 10/31/2006

So far I have:

Between 10/1/2006 and DateAdd("d", -Day(NOW()), NOW())

 

[AlexCuse]

This should get you in the right direction:

select convert(char(2),datepart(month, dateadd(m, -1,getdate()))) + '/01/' + convert(char(4),datepart(year, dateadd(m,-1,getdate())))

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[gmmastros]

Is this a SQL Server database? The syntax you show appears to be for Access.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

If it is a SQL Server database, then this syntax should work.

Select DateAdd(Month, DateDiff(Month, 0, GetDate())-1, 0)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ljevans]

It is a SQL server database, but the code that I used seems to work.

 

[gmmastros]

Uh... no. Now() is not a recognized function name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>Uh... no. Now() is not a recognized function name.

but it could be ;-)

 create function NOW()
returns int
as
begin
return 0
end

go

select dbo.now()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

some more fun

create function NOW()
returns table
as
return (select 0 as Now)
go

select now from now()
where now = now

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

In that case, (because of integer math), the query would evaluate to...

[tt][blue]
Between 10/1/2006 and DateAdd("d", -Day(NOW()), NOW())

Between 0 and DateAdd("d", 0, 0)

Between 0 and 0
[/blue][/tt]

So... Only records for Jan 1, 1900 (without a time component) would be returned.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ljevans]

But, I get the results I expect when I run the view.

 

[gmmastros]

ljevans,

Please do me a favor. Open Query Analyzer and run this...

Select @@Version

Then post the results back here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AngelB]

Guys,

I've just tried creating a view with that code in, and the view creator replaces it with:

DATEADD(d, - DAY({ fn NOW() }), { fn NOW() })

Which runs just fine in query analyzer. I have absolutely no idea why though!


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[ljevans]

Yes, in going back, I am seeing the same thing.

 

[SQLDenis]

The {fn Now()} is an ODBC canonical function
which can be used in T-SQL since the OLE DB provider for SQL Server supports
them.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AlexCuse]

Wow it is getting a little loopy in here today.

I think I will continue using getdate().

btw george, I really like your second post. It's much slicker than my clunker.

A wise man once said
"The only thing normal about database guys is their tables".

 

[SQLDenis]

GETDATE
Returns the current system date and time in the Microsoft® SQL Server standard internal format for datetime values.

CURRENT_TIMESTAMP
Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE.

Think about what Celko would tell you to use

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AlexCuse]

A wise man once said
"The only thing normal about database guys is their tables".

 

[SQLDenis]

I use GETDATE() all the time, primary reason is that it's shorter ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[AlexCuse]

Yeah I may stick with it too. They both come in the same format for me. I'll keep it in mind next time I am using sybase or anything like that though.

A wise man once said
"The only thing normal about database guys is their tables".

 

[ESquared]

I just talked to Celko this morning and he's as big an arrogant expletive as any rumors you may have heard.