Manipulating And Using DateTime Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » Manipulating And Using DateTime Data

Manipulating And Using DateTime Data

Rate Topic

Display Mode

Topic Options

Author

Message

Robert W Marda

Posted Friday, October 08, 2004 3:31 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Robert W. Marda
SQL Programmer
Ipreo

Post #140882

Renato Buda-153382

Posted Wednesday, October 27, 2004 5:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 09, 2013 8:29 AM
Points: 139, Visits: 218

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'

Post #143640

Bob Pearson

Posted Wednesday, October 27, 2004 8:29 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2008 8:24 AM
Points: 13, Visits: 2

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.

Post #143678

David Jackson

Posted Monday, November 01, 2004 9:09 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, March 08, 2013 11:15 AM
Points: 440, Visits: 1,785

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))

http://glossopian.co.uk/
"I don't know what I don't know."

Post #144153

Robert L

Posted Thursday, October 27, 2005 6:26 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 25, 2006 2:50 PM
Points: 16, Visits: 1

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

Post #232878

Luke L

Posted Thursday, October 27, 2005 6:47 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:24 PM
Points: 2,892, Visits: 5,871

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.

To help us help you read this

For better help with performance problems please read this

Post #232887

« Prev Topic | Next Topic »

Permissions