October 8, 2004 at 3:31 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 27, 2004 at 5:46 am
A good article with some interesting observations.
I would add a point that when using a string to represent the date it is better to get in the habit of using a format which is not dependant on the dateorder setting.
i.e. use
SET @Date = '20040930'
or
SET @Date = {d '2004-09-30'}
rather than
SET @Date = '9/30/2004'
October 27, 2004 at 8:29 am
One of the habits that I have developed, especially while developing date driven reports is to add this code to every stored procedure that I create:
--Include the entire End Date (Set time to 11:59)
Select @EndDate = DateAdd(d, 1,@EndDate)
Select @Enddate = cast(convert(varchar, @EndDate,101) as datetime)
Select @EndDate = DateAdd(s,-1,@EndDate)
--This is usually done in one statement, I broke it out for readability.
--Convert Start Date to midnight
SELECT @Startdate = cast(convert(varchar, @StartDate,101) as datetime)
This gives the full date range.
November 1, 2004 at 9:09 am
I sent this to one of our developers recently:
--to select all records dated 9th January 2004 in myTable_T use
select * from myTable_T
where dateQueued Between '09 Jan 2004' and '10 Jan 2004'
--which is equivilent to
select * from myTable_T
where dateQueued Between '09 Jan 2004 00:00:00' and '10 Jan 2004 00:00:00'
--note the time stamp is midnight on both days to get JUST the 9th use
declare @date datetime
set @date = '09 Jan 2004' --time defaults to midnight 00:00:00
select * from myTable_T
where dateQueued Between @date and DateAdd(ss,-1,dateAdd(dd,1,@date))
--to show what the dateadd code does above
select @date
select DateAdd(ss,-1,DateAdd(dd,1,@date))
October 27, 2005 at 6:26 am
Good point about sorting when date is stored in character datatype. I used to get various date points (i.e. first day of month, last day, etc...) by stripping the Year(), Month() and then stringing it back together, however I found this EXCELLENT article which generates all of these date points by strictly using built-in T-SQL functions. For example the last day of the month is: select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)), and of course you will commonly substitute a variable for the getdate() call here. Anyway read the article, the author has all the date points you will need: http://www.databasejournal.com/features/mssql/article.php/3076421
October 27, 2005 at 6:47 am
Good article, great for those new to T-SQL and also a great reminder for those of us that are slightly more seasoned. I deal with PostgreSQL alot and forget about how MS handles datetimes whenever I switch back to my MS SQL databases.
I'll definately be adding this one to my breifcase.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply