Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Manipulating And Using DateTime Data


Manipulating And Using DateTime Data

Author
Message
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 82
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/



Robert W. Marda
SQL Programmer
Ipreo

R M Buda
R M Buda
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 300
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
Bob Pearson
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 32

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
David Jackson
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1879
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
Robert L
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
Luke L
Luke L
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3370 Visits: 6123

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search