Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Manipulating And Using DateTime Data Expand / Collapse
Author
Message
Posted Friday, October 08, 2004 3:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/



Robert W. Marda
SQL Programmer
Ipreo
Post #140882
Posted Wednesday, October 27, 2004 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:23 PM
Points: 146, Visits: 235
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
Posted Wednesday, October 27, 2004 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, November 01, 2004 9:09 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 5:31 AM
Points: 441, Visits: 1,790
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
Posted Thursday, October 27, 2005 6:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 27, 2005 6:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:50 PM
Points: 2,897, Visits: 5,966

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 »

Add to briefcase

Permissions Expand / Collapse