August 19, 2009 at 2:03 pm
Good Day Gentlemen,
I have 4 date variables declared (BeginDate, LastBeginDate, ConcludeDate, LastConcludeDate), how can I set
BeginDate to have the "current month"/ "current day"/ "current year" and then 12 midnight (am) all in that order
ConcludeDate to have "current month"/ "current day"/ "current year and then current hour
and then
LastBeginDate to show the same time and format as BeginDate but for the same time last year
LastConcludeDate to show the same time and format as ConcludeDate but for the same time last year
Would I need to use any of the new date datatypes in 2008 (i would be very excited if so)
Thanks for any help!
August 19, 2009 at 2:09 pm
This will get you the data:
declare
@BeginDate datetime,
@LastBeginDate datetime,
@ConcludeDate datetime,
@LastConcludeDate datetime;
select
@BeginDate = dateadd(day, datediff(day, 0, getdate()), 0),
@ConcludeDate = dateadd(hour, datepart(hour, getdate()), dateadd(day, datediff(day, 0, getdate()), 0)),
@LastBeginDate = dateadd(year, -1, dateadd(day, datediff(day, 0, getdate()), 0)),
@LastConcludeDate = dateadd(year, -1, dateadd(hour, datepart(hour, getdate()), dateadd(day, datediff(day, 0, getdate()), 0)));
select @BeginDate, @ConcludeDate, @LastBeginDate, @LastConcludeDate;
As far as the formatting, let the front-end application/report do that for you. That's where formatting and appearances belong.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 2:18 pm
The internal storage of date and datetime information is actually an integer datatype, not a character string. However, it is presented as character string when you do a SELECT query and view it.
Within SQL, you can use the CONVERT function to change that character string into your format of choice.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 19, 2009 at 2:25 pm
Wow, that was money. Does it also take into consideration that the same time last year would have a different date (-1).
how do i tweaak it to return the last current hour? for example, if it is 2:01 till 2:59 am, could it return the hour till 2am and ignore the minute.
Do I get to learn about how to use the new date datatype?
As for the formatting, I will try using the convert (or is the cast more optimal)?
August 19, 2009 at 2:29 pm
The query I wrote will give you the hour without the minutes on the ones you want that way.
It also handles last year the way you asked.
You don't need to use the new data types for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 2:31 pm
Use CONVERT, because CAST doesn't give you all the formatting options.
The new datatypes aren't necessary unless you want to disregard time or store really ancient dates. But you can look at what each does in BOL (books online).
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply