SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / Manipulating And Using DateTime Data / Latest Posts

RE: Manipulating And Using DateTime Data

Luke L — Thu, 27 Oct 2005 06:47:00 GMT

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.

RE: Manipulating And Using DateTime Data

Robert L — Thu, 27 Oct 2005 06:26:00 GMT

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

RE: Manipulating And Using DateTime Data

David Jackson — Mon, 01 Nov 2004 09:09:00 GMT

I sent this to one of our developers recently:--to select all records dated 9th January 2004 in myTable_T useselect * from myTable_Twhere dateQueued Between '09 Jan 2004' and '10 Jan 2004'--which is equivilent toselect * from myTable_Twhere 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 usedeclare @date datetimeset @date = '09 Jan 2004' --time defaults to midnight 00:00:00select * from myTable_Twhere dateQueued Between @date and DateAdd(ss,-1,dateAdd(dd,1,@date))--to show what the dateadd code does aboveselect @dateselect DateAdd(ss,-1,DateAdd(dd,1,@date))

RE: Manipulating And Using DateTime Data

Bob Pearson — Wed, 27 Oct 2004 08:29:00 GMT

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.

RE: Manipulating And Using DateTime Data

Renato Buda-153382 — Wed, 27 Oct 2004 05:46:00 GMT

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. useSET @Date = '20040930'orSET @Date = {d '2004-09-30'}rather thanSET @Date = '9/30/2004'

Manipulating And Using DateTime Data

Robert W Marda — Fri, 08 Oct 2004 15:31:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/