time range query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

time range query

Author

Topic

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-07 : 07:58:52

hi all

i have a query to determine the time range like following

SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()

what time range that query set ??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-07 : 09:01:24

You can run it and see

DECLARE @StartDate DATETIME, @enddate DATETIME;
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate() 

SELECT @startDate, @endDate;

It is setting @startDate to midnight at the beginning of the current month and @EndDate to the current time.

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-08 : 06:27:09

hi james thanks for the reply

btw i also found the following

WHERE
( DateTime BETWEEN 41728 AND 41750 )

i dont understand the numbers meaning (41728 and 41750) are they a code for date on SQL environment or something else ??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-08 : 08:29:19

Integer data type is one of the data types that SQL Server will implicitly convert to Datetime if required. You can see all the gory details here: http://msdn.microsoft.com/en-us/library/ms191530.aspx You can see what 41728 represents by doing this:

SELECT CAST (41728 AS DATETIME)

However, in my opinion, it is a bad practice, both because it is hard to read (it is just as easy to write '20140401' instead of 41728), and because implicit conversions can result in poor performance in certain cases - for example, see here: http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources