Manipulating And Using DateTime Data

Question

Manipulating And Using DateTime Data

Robert W Marda

SSChampion

Points: 13413
More actions
October 8, 2004 at 3:31 pm

#165127

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

R M Buda SSCrazy Points: 2226 More actions · Answer 1

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'

Bob Pearson SSC Rookie Points: 45 More actions · Answer 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.

David Jackson SSCertifiable Points: 6414 More actions · Answer 3

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."

Robert L SSC Journeyman Points: 90 More actions · Answer 4

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

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[/url]For better help with performance problems please read this[/url]

Manipulating And Using DateTime Data

Cookies on SQLServerCentral