Building a table with Dates from date formulas
-
I want to create a table populated with dates, it will be just one row updated daily, I need formulas to create like first day of month, last day of month. first day of current year, last day of current year, first day of week, last day of week, I need these to populate the table so I can use them in a view to populate calculations built on those fields. I hope that makes sense. In access I just wrote the module then ran a query to populate the table everyday.
Regards
-
Try this:
declare @d datetime
select @d = '01/03/02'
-- first day of month
-- select cast( convert( char(6), @d, 112) + '01' as datetime)
-- last day of month
-- select dateadd( day, -1, dateadd( month, 1, cast( convert( char(6), @d, 112) + '01' as datetime)))
-- first day of year
-- select cast( convert( char(4), @d, 112) + '0101' as datetime)
-- last day of year
-- select dateadd( day, -1, dateadd( year, 1, cast( convert( char(4), @d, 112) + '0101' as datetime)))
What do you mean by the day of week? Is it Sunday? Mon? Do you want the date? Or the day?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
or try these:
ALTER FUNCTION dbo.FirstDay (@DateValue smalldateTime)
RETURNS smallDateTime
AS
BEGIN
select @DateValue = dateadd(dd, -(day(@datevalue)-1), @datevalue)
return @DateValue
END
CREATE FUNCTION dbo.LastDay
(
@DateValue SmallDateTime
)
RETURNS SmallDateTime
AS
BEGIN
select @DateValue = dateadd(dd, -(day(@datevalue)), dateadd(mm, 1, @datevalue))
return @DateValue
END
-
I hope I'm not doing your homework for you Troy. This should get you to the first day of the week:
@@DateFirst tells you what sql server is set to use as the first day of the week. The default is 7, Sunday.
DatePart(dw, MyDate) tells you how many days past @@DateFirst you are. If the first day of the week is Sunday, and MyDate is a Sunday, DatePart(dw, MyDate) will return 1. Monday will be 2 and so on. Therefore, to calculate the first day of the week we can do this:
declare @DateValue smalldatetime
set @Datevalue = GetDate()
select dateadd(dd, -(datepart(dw, @DateValue)-1), @DateValue)
I'll leave the year calculations to you.
Have fun!
John
-
No it's not my homework, just knew I could do it, but I had a brain cramp. Not much different than what I thought, looks like ya really got to spell it out.
-
good luck. post if you have more questions.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I'm a newbie (as if you couldn't tell) any web based or anything I can get a crash course...lol...
Regards
Joe
(A Wanna be developer overnight...lol)
-
Thanks for asking that question. I can use a lot of places to use that code! I am trying to learn basic SQL using Joe Celko's "Instant SQL Programming" on SQL Server 7 and ran into the old datetime hitch when trying to enter the sample tables. I need a simple current date entry (used as a default date for an orders table column). I used the timestamp but am afraid that isn't right. Is there some kind of current date or typical code for producing it?
Sivea -
The getdate() function will return the current date and time.
Andy
-
Thanks! I think I need a "dictionary" of terms. I have "Inseide SQL Server 2000" but it doesn't fit the bill. Any suggestions? I'm ultimately aiming for SQL Server 2k designing--I am just trying to get a handle on generic code before I get immersed in the land of the wizards. I come from file based database programming and have NO familiarity with Access or any other table based software.
Sivea -
If you have SQL Server 7, look at the Books Online documentation that installs with the software by default (though you can select not to install it). It's a very good reference for this sort of thing and it's likely to become your best friend as a DB programmer or DBA. Microsoft's documentation isn't always up to par, but they've done a good job with the Books Online for both SQL Server 7 and 2000.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Is the BOL separate from the help file? I have tried searches in help with little "joy" but perhaps I'm not asking questions well. For example, I tried to find info on dates and current dates and found some rather irrelevant, generic information.
Sorry for bothering y'all with such rudimentary questions but that's where I'm getting stuck--trying to translate what I am familiar with in to this new scheme.
Thanks again!
Sivea -
It is a help file, but from the Start menu it's titled Books Online:
Most of the time I go to the index section when looking for something. For instance, looking for date brought the following (this is SQL 2K's Books Online, but for the most part it is the same as 7):
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 01/26/2002 5:56:39 PM
K. Brian Kelley
@kbriankelley -
VERY nice! That's just the kind of info I was looking for. Thank you for teaching me to fish! I like fishing and always appreciate tips to good holes. I suppose I should have found it on my own--guess I became a bit myopic trying to match Celko's examples using Watcom SQL 4.0 runtime engine.
Sivea -
Celko's contributions to the SQL community are well-documented, but I agree that his writing can be a little difficult to read.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply