convert DateSerial from access

[mbair]

I have the following DateSerial command in access:

DateSerial(Year(DateSerial(Year(Now()),Month(Now()),1)-1)-1,1,1) And DateSerial(Year(Now())-1,Month(Now()),1)-1

I need to convert it to work in a SQL server view.

 

[SQLSister]

What does dateserial do? Please show the data result you wantand what the data you are starting with looks like.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Try these....

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Year[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Year[/color], 0, [COLOR=#FF00FF]GetDate[/color]()) - 1, 0)

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Year[/color], -1, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0) - 1)

I strongly suggest that you read up on dates. The way dates are handled in SQL Server is considerably different than Access.

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

SQLSister,

When I run those DateSerial functions in VB, I get this...

? DateSerial(Year(DateSerial(Year(Now()),Month(Now()),1)-1)-1,1,1)
1/1/2006 

? DateSerial(Year(Now())-1,Month(Now()),1)-1
6/30/2006

-George

"the screen with the little boxes in the window." - Moron

 

[SQLSister]

I think we have an FAQ on dates, you should go read through it. It might help considerably.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

Yes, this one

http://tek-tips.com/faqs.cfm?fid=5842