Daylite saving time problem

Hello
I am using SQL Server 2000, SP4
I am calculating number of hours passed between two dates. Both dates have
time set to 00:00:00. I use datediff function it works ok unless the time
interval I pass includes date when time is changed due to Daylite Saving Tim
e
(DST) issue. Instead of one hour more or one hour less datediff keeps
returning constant number of hours.
Does SQL Server 2000 internally support DST depending on a regional settings
in OS?
Thanks in advance.we don't that feature in SQL Server to my knowledge. You can write a UDF to
do the conversion.
Check out this link
http://www.planet-source-code.com/U...cripts/ShowCode!asp/txtCodeId!9
11/lngWid!5/anyname.htm|||Thanks a lot|||Some ideas here maybe:
http://www.aspfaq.com/2218
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||Oh, and also the calendar table.
http://www.aspfaq.com/2519
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||or how about rather than using getdate() to get the two dates in the
first place, use getutcdate() function?
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal
Time Coordinate or Greenwich Mean Time). The current UTC time is
derived from the current local time and the time zone setting in the
operating system of the computer on which SQL Server is running.
Mel|||> or how about rather than using getdate() to get the two dates in the
> first place, use getutcdate() function?
> GETUTCDATE
> Returns the datetime value representing the current UTC time (Universal
> Time Coordinate or Greenwich Mean Time). The current UTC time is
> derived from the current local time and the time zone setting in the
> operating system of the computer on which SQL Server is running.
Well, if you're comparing two datetime values:
2005-12-31
2006-06-01
If you're in a timezone that observes daylight savings time, your
calculation is going to be an hour off (which way depends on what is
currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
an hour off in the other direction the next time the daylight savings time
goes on or off.
The calendar table can help solve this problem by giving you the offset on
each of the dates in question, allowing you to adjust each date accordingly.|||You also have to take into account that different areas change their clocks
on different dates, so you may need to create a second table with each time
zone and the date/time that they change their clocks.
That, and some areas (Arizona for example) do not use daylight savings time
at all.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OES60M7YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Well, if you're comparing two datetime values:
> 2005-12-31
> 2006-06-01
> If you're in a timezone that observes daylight savings time, your
> calculation is going to be an hour off (which way depends on what is
> currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
> an hour off in the other direction the next time the daylight savings time
> goes on or off.
> The calendar table can help solve this problem by giving you the offset on
> each of the dates in question, allowing you to adjust each date
accordingly.
>|||> You also have to take into account that different areas change their
> clocks
> on different dates, so you may need to create a second table with each
> time
> zone and the date/time that they change their clocks.
Or an extra column for each timezone (reproduce the tinyints instead of the
wider date values).

> That, and some areas (Arizona for example) do not use daylight savings
> time
> at all.
Right, Indiana just changed. Next year, the formula for determining the
dates changed in the US, so I think a lot of people who hav used an inline
calculation for this are either already working on fixing it or have plenty
of work to do over the winter. Since we used a calendar table in all of our
implementations, we don't have to worry about it... a simple update
statement corrects all future data until they waffle again.|||a column for each timezone seems much more complex than a single table with
one row each.
However, the benefit to doing it with columns is that you don't run into
problems when the timezone rules change. In the case of Indiana, you would
update the Indiana column in the calendar table for those date ranges. With
a separate table you would need to store the date that the rules changed and
always make sure you are joining to the correct row. I think I like your
idea of multiple columns better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O%23NxvE8YGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Or an extra column for each timezone (reproduce the tinyints instead of
the
> wider date values).
>
> Right, Indiana just changed. Next year, the formula for determining the
> dates changed in the US, so I think a lot of people who hav used an inline
> calculation for this are either already working on fixing it or have
plenty
> of work to do over the winter. Since we used a calendar table in all of
our
> implementations, we don't have to worry about it... a simple update
> statement corrects all future data until they waffle again.
>

datetime in SQL Server 2000 SP2

Hello!
I have datetime column <CreatedDate> in table <sale>.
Here' s some sample data for CreatedDate:
2004-04-30 15:56:12.390
2004-04-30 15:59:42.000
2004-04-30 15:58:42.100
2004-04-30 16:01:06.190
2004-04-30 16:01:59.820
When running query below I'm not getting any rows back:
select name, CreatedDate
from sale
where CreatedDate between '04/01/2004' and '04/30/2004'
But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
From BOL:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Why sql server can't recognize '04/30/2004'?
Thanks,
Lena
Because '04/30/2004' really means '04/30/2004 00:00:00.000' and '2004-04-30
15:56:12.390' is greater than that.
Bojidar Alexandrov
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or
equal to the value of begin_expression and less than or equal to the value
of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||I suggest you check out my article about datetime at:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of
begin_expression and less than or equal to the value of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||Is there a way to use some date function in where clause so sql server will return all rows with CreatedDate for 04/30/2004 and will ignore time stamp?
Thanks
|||Lena,
This issue(plus many others) are discussed in the link that Tibor posted.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:BC32384D-6877-4DCF-9861-712082375A2D@.microsoft.com...
> Is there a way to use some date function in where clause so sql server
will return all rows with CreatedDate for 04/30/2004 and will ignore time
stamp?
> Thanks

datetime in SQL Server 2000 SP2

Hello!
I have datetime column <CreatedDate> in table <sale>.
Here' s some sample data for CreatedDate:
2004-04-30 15:56:12.390
2004-04-30 15:59:42.000
2004-04-30 15:58:42.100
2004-04-30 16:01:06.190
2004-04-30 16:01:59.820
When running query below I'm not getting any rows back:
select name, CreatedDate
from sale
where CreatedDate between '04/01/2004' and '04/30/2004'
But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for Cr
eaedDate '04/30/2004'.
From BOL:
BETWEEN returns TRUE if the value of test_expression is greater than or equa
l to the value of begin_expression and less than or equal to the value of en
d_expression.
Why sql server can't recognize '04/30/2004'?
Thanks,
LenaBecause '04/30/2004' really means '04/30/2004 00:00:00.000' and '2004-04-30
15:56:12.390' is greater than that.
Bojidar Alexandrov
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or
equal to the value of begin_expression and less than or equal to the value
of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>|||I suggest you check out my article about datetime at:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or equal to t
he value of
begin_expression and less than or equal to the value of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>|||Is there a way to use some date function in where clause so sql server will
return all rows with CreatedDate for 04/30/2004 and will ignore time stamp?
Thanks|||Lena,
This issue(plus many others) are discussed in the link that Tibor posted.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:BC32384D-6877-4DCF-9861-712082375A2D@.microsoft.com...
> Is there a way to use some date function in where clause so sql server
will return all rows with CreatedDate for 04/30/2004 and will ignore time
stamp?
> Thanks

datetime datatype conversion

Hello

I have 1 column in table with char datatype that stores datetime values (e.g.: 20061207091510 which translates to 2006-12-07 09:15:10).

Is there a way to convert this string into datetime datatype to preserve the time part (hours:minutes:seconds?

Thanks,

Lena

SELECT CONVERT(DATETIME, LEFT('20061207091510', 8), 112)+CONVERT(DATETIME, SUBSTRING('20061207091510', 9, 2) + ':' + SUBSTRING('20061207091510', 11, 2) + ':' + SUBSTRING('20061207091510', 13, 2), 114)

|||thank you!

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um
tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de
um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/in...ime.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) a
nd this is okay on one
> server (which may have British language settings) but not on another (whic
h may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates
as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2
MSFTNGP03.phx.gbl...
>

Dateproblem

Hello
I'm trying to get data from a view and it's not going well, im trying to
"group" the rows abit so I dont end up with a million rows to display a date
range....
Hope this makes sense....
CREATE TABLE #Test (
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(Startdate,Enddate,Avalible)
SELECT
'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Testerr... use this DLL instead..
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,Avalible)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse Edsvik wrote:

> I'm trying to get data from a view and it's not going well, im trying
> to "group" the rows abit so I dont end up with a million rows to
> display a date range....
> Hope this makes sense....
I once had the same problem and question asked: http://tinyurl.com/7gnst
HTH,
Stijn Verrept.|||Or this if it makes it more simple.... :S
CREATE TABLE #Test (
ObjectID int,
Wkday tinyint,
StartTime char(5),
EndTime char(5),
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Wkday,StartTime,EndTime,Avalib
le)
SELECT
1,1,'13:00','13:30','Y'
UNION SELECT
1,1,'13:30','14:00','Y'
UNION SELECT
1,1,'14:00','14:30','N'
UNION SELECT
1,1,'14:30','15:00','N'
UNION SELECT
1,1,'15:00','15:30','Y'
UNION SELECT
1,1,'15:30','16:00','Y'
UNION SELECT
1,1,'16:00','16:30','N'
UNION SELECT
1,2,'15:00','15:30','Y'
UNION SELECT
1,2,'15:30','16:00','N'
SELECT * FROM #Test
/*
Desired result:
ObjectID Wkday StartTime EndTime
1 1 '13:00' '14:00'
1 1 '15:00' '16:00'
1 2 '15:00' '15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse,
select ObjectID,Startdate, coalesce(last_available, enddate) enddate
from
(
SELECT t.*,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='Y') prev_available,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='N')
prev_not_available,
(select max(enddate) FROM #Test t1
where t1.startdate>t.startdate and t1.available='Y'
and not exists(select 1 from #test t2
where t2.available='N' and t2.startdate between t.startdate and
t1.startdate)
) last_available
FROM #Test t
where available='Y'
) t
where prev_available<prev_not_available
or prev_available is null
ObjectID Startdate
enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
(3 row(s) affected)
Nice puzzle, thanks!|||hmm,
Server: Msg 207, Level 16, State 3, Line 30
Invalid column name 'available'.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139324776.706400.210190@.f14g2000cwb.googlegroups.com...
> Lasse,
> select ObjectID,Startdate, coalesce(last_available, enddate) enddate
> from
> (
> SELECT t.*,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='Y') prev_available,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='N')
> prev_not_available,
> (select max(enddate) FROM #Test t1
> where t1.startdate>t.startdate and t1.available='Y'
> and not exists(select 1 from #test t2
> where t2.available='N' and t2.startdate between t.startdate and
> t1.startdate)
> ) last_available
> FROM #Test t
> where available='Y'
> ) t
> where prev_available<prev_not_available
> or prev_available is null
> ObjectID Startdate
> enddate
> -- ---
> ---
> 1 2006-01-31 13:00:00.000
> 2006-01-31 14:00:00.000
> 1 2006-01-31 15:00:00.000
> 2006-01-31 16:00:00.000
> 1 2006-02-01 15:00:00.000
> 2006-02-01 15:30:00.000
> (3 row(s) affected)
> Nice puzzle, thanks!
>|||yes the spell checker replaced Avalible with Available. I did not argue
with it ;)|||Alexander,
oh k :) You have any idea why ObjectID=2 doesnt show up?
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
available char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,available)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
2,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
2,'2006-01-31 14:00','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-02 15:00','2006-02-02 15:30','Y'
UNION SELECT
1,'2006-02-02 16:00','2006-02-02 16:30','Y'
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t1.startdate>t.startdate and t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.Available='N' and t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139325716.672360.263040@.g47g2000cwa.googlegroups.com...
> yes the spell checker replaced Avalible with Available. I did not argue
> with it ;)
>|||because I did not think of ObjectId at all. It's easy to take care of
however:
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate>t.startdate and
t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.ObjectID=t1.ObjectID and t2.Available='N' and
t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
ObjectID Startdate
Enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
2 2006-01-31 13:30:00.000
2006-01-31 16:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
1 2006-02-02 15:00:00.000
2006-02-02 16:30:00.000
(5 row(s) affected)

DATEDIFF and time format in Sql Server

Hello;
I'm attempting to use the datediff method to compare two dates,
generated under visual studio 2005 with the instruction
DateTime.Now.ToLocalTime().ToString(), which returns something like DD-
MM-YYYY HH:MM:SS.
the dates are then stored in an sql server database and then a query
returns some results based on the difference between two given dates
using the datediff instruction.
the problem is that SQL Server interprets the time as being MM-DD-YYYY
instead of DD-MM-YYYY, which means an query like
SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007
11:11:11') AS Expr1
FROM <table>
will return 1 instead of 2.
the sql server 2005 i'm using the the one that comes with VS2005, it's
not the stand alone version. i've tried looking into some settings
hoping to fix this, but i've had no luck this far.
how can i change the way sql server reads a date, or how can i "fool"
him using some other method?
thanks in advance!A quick fix for this would be to use SET DATEFORMAT to change the current
interpretation of character strings when they are converted to date values.
Something like this:
SET DATEFORMAT dmy
GO
SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007 11:11:11')
That should give you as result 2, which is what you expect. Alternatively
you can use SET LANGUAGE which will set the format according for the
language selected.
However, the correct way to fix this is:
1. In your Visual Studio application pass the date to SQL Server as a Date
data type (not string)
2. In SQL Server store the date in a datetime column type
That way dates will be always treated properly, plus you can benefit of
using the date/time functions directly with no conversion.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for the answer!
There's more than one solution, and i'm pleased with that already!
But if the Datetimes provided by Datetime.Now.ToLocalTime() are in the
DD-MM-YYYY format, even if i store them as Datetime in the database,
won't the problem remain still? I always have to compare the dates
within the database with those provided by that instruction...
Unless i'm making some confusion in my head, datediff always uses
(unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
regardless if it's stored as datetime or string, right? I don't want
to compare two dates within the database, but alwas between a stored
value and a current value (from the c# 's datetime).
The actual instruction (without your suggested changes) is something
like:
SELECT <titles> FROM <table> WHERE <conditions> AND (datediff(second,
<date stored>,'" + DateTime.Now.ToLocalTime().ToString() + "')>20)
Thanks once again!
On Mar 15, 3:02 am, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> A quick fix for this would be to use SET DATEFORMAT to change the current
> interpretation of character strings when they are converted to date values
.
> Something like this:
> SET DATEFORMAT dmy
> GO
> SELECT DATEDIFF(month, '11-2-2007 11:11:11', '12-4-2007 11:11:11')
> That should give you as result 2, which is what you expect. Alternatively
> you can use SET LANGUAGE which will set the format according for the
> language selected.
> However, the correct way to fix this is:
> 1. In your Visual Studio application pass the date to SQL Server as a Date
> data type (not string)
> 2. In SQL Server store the date in a datetime column type
> That way dates will be always treated properly, plus you can benefit of
> using the date/time functions directly with no conversion.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com|||"zainab" <pedralm@.gmail.com> wrote in message
news:1173930670.588966.235330@.o5g2000hsb.googlegroups.com...
> Thank you for the answer!
> There's more than one solution, and i'm pleased with that already!
> But if the Datetimes provided by Datetime.Now.ToLocalTime() are in the
> DD-MM-YYYY format, even if i store them as Datetime in the database,
> won't the problem remain still? I always have to compare the dates
> within the database with those provided by that instruction...
> Unless i'm making some confusion in my head, datediff always uses
> (unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
> regardless if it's stored as datetime or string, right? I don't want
> to compare two dates within the database, but alwas between a stored
> value and a current value (from the c# 's datetime).
> The actual instruction (without your suggested changes) is something
> like:
> SELECT <titles> FROM <table> WHERE <conditions> AND (datediff(second,
> <date stored>,'" + DateTime.Now.ToLocalTime().ToString() + "')>20)
>
Ok, this makes things different. In C# I believe you can do something like
this:
DateTime.Now.ToLocalTime().ToString("MM/dd/yyyy HH:mm:ss")
That should format the date/time to match the current SQL Server format.
A better solution will be to create a stored procedure with datetime
parameter and to pass the date from C# as datetime, like
DateTime.Now.ToLocalTime() without converting to string. Then as long as the
column of the table in SQL Server is datetime type you do not have to worry
about the format of the date. Datetime type is compatible and will always be
interpreted correctly.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||> Unless i'm making some confusion in my head, datediff always uses
> (unless i use that other suggestion) MM-DD-YYYY over DD-MM-YYYY,
> regardless if it's stored as datetime or string, right?
Wrong. Datetime values are not stored in ANY readable format. If you
intend to represent datetime constants as strings in your tsql code (either
directly or indirectly via the code/functions generated/provided by VS),
then you should understand how these strings are interpreted and how to use
them correctly.
http://www.karaszi.com/sqlserver/info_datetime.asp|||Thank you both for your replies!
By using a simple "SET DATEFORMAT dmy" before my instruction, as
suggested by Plamen Ratchev, i had my problem instantly fixed. I didnt
have to change the table settings as this is the only use i give to
this field (besides presenting the value, where keeping it as a string
made it simpler for me).
According to Scott Morris' link:
The Numeric format (the one i was using) can use dash (-), dot (.) or
slash (/) as separator. The rules for how SQL Server parses the string
doesn't change depending on the separator. A common misconception is
that the ANSI SQL format (sometime a bit incorrectly referred to as
the "ISO format"), 1998-02-23, is language neutral. It isn't. It is a
numeric format and hence it is dependent on the SET DATEFORMAT and SET
LANGUAGE setting
SET DATEFORMAT inherits its setting from SET LANGUAGE (but an explicit
SET DATEFORMAT will override later SET LANGUAGE).
so it was pretty clear that all i had to do was indeed SET DATEFORMAT
dmy!
thank you!
ps: sorry for the "explanation", but sometimes it's useful in the
future for people who run into the same problems.

datediff (today - date)

Hello

I want to return the number of days between a date in the database and today

something like

SELECT user.fName,
user.lName & " (" & (datediff(now - user.lastVisit)) & " )"
FROM user


I must return

John Turner (38)

where 38 are the days between last visit and now

thank yousee this,now I think you can do it easily

declare @.datevar datetime
select @.datevar = getdate()


*Example for datediff : getting no of days passed since 01-01-2006*/

select datediff(dd,'20060101',@.datevar) [No of days since 01-01-2006]|||it works !

thanks a lot