date from datetime field

Hi

What is the best practice to get the date from a smalldatetime field without the time.

The table contains 5 minute readings for energy consumption in the column period.

Now i need to get all the readings form some dates.

SELECT dbo.TBL_Data.*
FROM dbo.TBL_Data
WHERE (Period IN (CONVERT(DATETIME, '2003-12-31', 102), CONVERT(DATETIME, '2004-01-01', 102)))

this result contains only the readings for the timestamp 00:00

so how to select the whole day ?

kind regards

piet1. "IN" keyword only checks if the values are EQUAL to any of the values listed, not in between the two values (check for the BETWEEN operator or use a combination of greater-than and less-than), and

2. Also try explicitly setting the time value for each convert (check my syntax) so that you don't rely on the default time setting:

WHERE Period > CONVERT(DATETIME, '2003-12-31 00:00:00', 102) AND Period < CONVERT(DATETIME, '2003-12-31 12:59:59', 102)

Date and Time Best Practice - storage in SQL Server database

With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?

For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.

My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?

Thanks in advance for your help!

PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.

Hey, this is an old song.

My point is that you have to store date in a universal format - UTC, this will prevent any troubles while moving database on another zone.

Next, your users have to setup theirs profile and specify in what timezone they are in. According to this you're making shifts.

|||

E.g. look at this site, each user has Site Option section where he/she can setup time zone.

|||

I guess it is that simple.

Do you know of any webservice or other source that has time zone data?

Thanks.

|||

Here is a good article on the subject:

http://aspnet.4guysfromrolla.com/articles/081507-1.aspx