Days between Two Dates.

Hi all,

I have a table in which I have two fields in my DB.

FromDate and ToDate.

Both are stored as Varchar(MAX).

I would like to have an SP which gives me the Days in Between them.

Regards,

Naveen.

try this:

selectdatediff(day,convert (datetime ,'07/05/2007'),convert (datetime ,'07/06/2007'))as [day diff]

|||

Hi addie,

what I want is an SP.

Regards,

Naveen

|||

ifexists (select *fromsysobjectswhere id =object_id ('sp_test'))drop proceduresp_testgocreate proceduresp_test@.start_datevarchar(20),@.end_datevarchar(20)asselectdatediff(day,convert (datetime , @.start_date),convert (datetime , @.end_date))as [day diff]

days between dates from a list

Hi,
I am trying to perform an interpolation of counts between event dates...my
data looks like this:
Event Date Count
1/1/06 13
1/17/06 9
2/3/06 7 etc...
The spacing of event date is not always equal thus I need to be able to do
something like this: (date1-nextdate). I don't know how to select the next
date. Any help if greatly appreciated.
Jen...learning
This might work and be fast if event date is a PK or indexed:
SELECT
E.[EventDate], E.[CountOfThings], dbo.ufn_NextEvent(E.[EventDate]) AS
NextDate
FROM
Events E
Where dbo.ufn_NextEvent is a user defined function like:
CREATE FUNCTION [dbo].[ufn_NextEvent]
(
@.ThisEvent DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.result DATETIME
SELECT TOP 1 @.result = [EventDate] FROM Events WHERE [EventDate] > @.ThisEvent
RETURN (@.result)
END
Result set is:
2006-01-01 00:00:00.000132006-01-17 00:00:00.000
2006-01-17 00:00:00.00092006-02-03 00:00:00.000
2006-02-03 00:00:00.0007NULL
Regards,
JayAchTee
"jennifer.heintz" wrote:

> Hi,
> I am trying to perform an interpolation of counts between event dates...my
> data looks like this:
> Event Date Count
> 1/1/06 13
> 1/17/06 9
> 2/3/06 7 etc...
> The spacing of event date is not always equal thus I need to be able to do
> something like this: (date1-nextdate). I don't know how to select the next
> date. Any help if greatly appreciated.
> --
> Jen...learning

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.
>

Daylight savings time

Does anyone have a good daylight savings time function? I need to get it going today, and am thinking of being lazy and just putting the dates in a table for the next several years. Since I am only concerned with EST and BST, however, and both follow strict rules, I was hopeing to write a function that I can use dynamically.

Hate to reinvent the wheel though.

TIAI have one I can send you later today.

blindman|||You da (blind)man!|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BeginDST]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[BeginDST]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EndDST]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EndDST]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create function BeginDST(@.TargetDate as datetime)
returns datetime
as
--function BeginDST
--blindman, 9/2003
--Returns the data Daylight Savings Time begins for the specified year.
begin
declare @.BeginDST datetime
set @.BeginDST = '4/1/' + cast(Year(@.TargetDate) as char(4))
while datename(weekday,@.BeginDST) <> 'Sunday'
set @.BeginDST = dateadd(day, +1, @.BeginDST)
Return @.BeginDST
end

GO

create function EndDST(@.TargetDate as datetime)
returns datetime
as
--function EndDST
--blindman, 9/2003
--Returns the data Daylight Savings Time ends for the specified year.
begin
declare @.EndDST datetime
set @.EndDST = '10/31/' + cast(Year(@.TargetDate) as char(4))
while datename(weekday,@.EndDST) <> 'Sunday'
set @.EndDST = dateadd(day, -1, @.EndDST)
Return @.EndDST
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Thanks dude. While I was waiting, however, I came up with this:

CREATE FUNCTION [TZCONVERT] (@.time_zone VARCHAR(5), @.in_date DATETIME)
RETURNS DATETIME AS
BEGIN

DECLARE @.out_date DATETIME,
@.daylight_start_date DATETIME,
@.daylight_end_date DATETIME

IF @.time_zone = 'EST'
BEGIN
SET @.daylight_start_date = DATEADD(hour, 3, DATEADD(d, (7-DATEPART(dw,'4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4)))+2)%7-1, '4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))
SET @.daylight_end_date = DATEADD(hour, 1, DATEADD(day, -1 * DATEPART(dw,'10/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '11/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))

IF @.in_date BETWEEN @.daylight_start_date AND @.daylight_end_date
SET @.out_date = DATEADD(hour, -4, @.in_date)
ELSE
SET @.out_date = DATEADD(hour, -5, @.in_date)
END

IF @.time_zone = 'BST'
BEGIN
SET @.daylight_start_date = DATEADD(hour, 3, DATEADD(day, -1 * DATEPART(dw,'3/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '4/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))
SET @.daylight_end_date = DATEADD(hour, 1, DATEADD(day, -1 * DATEPART(dw,'10/31/' + CAST(YEAR(@.in_date) AS VARCHAR(4))), '11/1/' + CAST(YEAR(@.in_date) AS VARCHAR(4))))

IF @.in_date BETWEEN @.daylight_start_date AND @.daylight_end_date
SET @.out_date = DATEADD(hour, 1, @.in_date)
ELSE
SET @.out_date = @.in_date
END

RETURN @.out_date

END|||I tried using modulo arthimetic at first too, but it became too confusing to try to account for the fact that the "dw" parameter for DATEPART returns different values on different systems depending on the value of the DATEFIRST setting.

As long as you never run your code on a system with a different setting you should be OK.

blindman

DAY() not working with 'Left Join'?

I have two tables. Days (1-31) and dates (random dates)

If I have a query that is

Select Day, Date

From days LEFT JOIN dates ON days.Day = DAY(dates.date)

Order By Day, Date

The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?

Thanks.

ry this:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

|||That didn't seem to do anything. What was the thought behind this if you don't mind?|||

CREATE TABLE [dbo].[Dates]([Date] [datetime] NULL,

[id] [int] NULL)

INSERT INTO [Dates] ([Date],[id])VALUES('Oct 2 2006 12:00:00:000AM',1)
INSERT INTO [Dates] ([Date],[id])VALUES('Oct 4 2006 12:00:00:000AM',2)

CREATE TABLE [Days]([Day] [int] NULL)

INSERT INTO [Days] ([Day])VALUES(1)
INSERT INTO [Days] ([Day])VALUES(2)
INSERT INTO [Days] ([Day])VALUES(3)
INSERT INTO [Days] ([Day])VALUES(4)
INSERT INTO [Days] ([Day])VALUES(5)
INSERT INTO [Days] ([Day])VALUES(6)
INSERT INTO [Days] ([Day])VALUES(7)
INSERT INTO [Days] ([Day])VALUES(8)
INSERT INTO [Days] ([Day])VALUES(9)
INSERT INTO [Days] ([Day])VALUES(10)
INSERT INTO [Days] ([Day])VALUES(11)
INSERT INTO [Days] ([Day])VALUES(12)
INSERT INTO [Days] ([Day])VALUES(13)
INSERT INTO [Days] ([Day])VALUES(14)
INSERT INTO [Days] ([Day])VALUES(15)
INSERT INTO [Days] ([Day])VALUES(16)
INSERT INTO [Days] ([Day])VALUES(17)
INSERT INTO [Days] ([Day])VALUES(18)
INSERT INTO [Days] ([Day])VALUES(19)
INSERT INTO [Days] ([Day])VALUES(20)
INSERT INTO [Days] ([Day])VALUES(21)
INSERT INTO [Days] ([Day])VALUES(22)
INSERT INTO [Days] ([Day])VALUES(23)
INSERT INTO [Days] ([Day])VALUES(24)
INSERT INTO [Days] ([Day])VALUES(25)
INSERT INTO [Days] ([Day])VALUES(26)
INSERT INTO [Days] ([Day])VALUES(27)
INSERT INTO [Days] ([Day])VALUES(28)
INSERT INTO [Days] ([Day])VALUES(29)
INSERT INTO [Days] ([Day])VALUES(30)
INSERT INTO [Days] ([Day])VALUES(31)

And the script that works:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

If you cannot run this, let's see what is the problem again.

|||

So I get to playing around with your example and descovered some stuff I didn't know about left joins.

My query has touble when I add a Where clause on it to filter dates to a certain range. The differents querys are below in case someone else needs help. Thanks.

NOT WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date)

Where b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date) and

b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

|||

If you use a subquery with a where clause fro your LEFT JOIN, it should work.

Select a.Day, b.date

From days a LEFT JOIN (select * FROM dateshiftcrewTable Where date Between '1/1/1999' and '4/4/1999') b ON a.Day = DAY(b.date)

Order By a.Day, b.date

Day Of The Week Aggregate

I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:

>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP

Day Of The Week Aggregate

I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be great
Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
>
|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:

>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP

Day Of The Week Aggregate

I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>> I'm looking for a way to turn a list of dates into a string of
>> abbreviations
>> 10/9/2006
>> 10/11/2006
>> Would Translate To
>> M-W
>> Any Ideas would be great
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> > I'm looking for a way to turn a list of dates into a string of
> > abbreviations
> >
> > 10/9/2006
> > 10/11/2006
> > Would Translate To
> > M-W
> > Any Ideas would be great
> >|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
--
Hugo Kornelis, SQL Server MVP

DateTime unable to save in datetime field of SQL database

Hi all, having a little problem with saving dates to sql database

I've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up

Error "

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

I've tried researching it but not been able to find something similar.

Heres the code:

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')";

Any help would be much appreciated

If you are using SQL Server, change the statement to

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',GetDate())

If you are using Access then use this:

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',Date())

|||

Sorry, my fault i should have said, i'm coding in c sharp, heres the expanded function

void AddToQuizIndex(String userName,String quizName,String description,String question_xml)

{

DateTime createOn = DateTime.Now;

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"','" +createOn+"','" + question_xml +"')";this.ActionSQLStatement(sSQLStatement);

}

|||

C# makes no difference. GetDate() in SQL Server will automatically apply the equivalent of C# datetime.now. But your database won't complain. Try it.

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"',GetDate(),'" + question_xml +"')";

Really, you should be using parameters rather than compiling dynamic SQL statements, but that's another topic.

|||

nice one, first time i tried it i didn't put ' ' round the GetDate()

Thanks very much for the replyMikesdotnetting, you really helped me out.

DateTime null in Sql Server database

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Are you sure you're referencing the correct Parameter? The error message says "@.DataRDV" but your code uses "@.Date".

|||

GranPas wrote:

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Did you add a parameter called "@.DateRDV"?

|||

Thanks for help. I actually changed my variable name which was DateRDV to Date because the source code i had pasted was from a sample i found on Internet.

I found a solution to my problem. When the user click on a button, I set myDate to DateTime.MinValue if myDate is null, as i did before. Now, I am using a function in order to insert the date in my database. This is working and I still don't know why the older source code did not. Here is my source working :

int Insert_Trdv(System.DateTime dateRDV)
{
string connectionString = "server=\'myServer\'; user id=\'myId\';
password=\'myPassword\'; database=\'myPassword\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = @."INSERT INTO [Trdv] ([DateRDV])";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_dateRDV = new
System.Data.SqlClient.SqlParameter();
dbParam_dateRDV.ParameterName = "@.DateRDV";
if(dateRDV == DateTime.MinValue)
{
dbParam_dateRDV.Value = DBNull.Value;
}
else
{
dbParam_dateRDV.Value = dateRDV;
}
dbParam_dateRDV.DbType = System.Data.DbType.DateTime;
dbCommand.Parameters.Add(dbParam_dateRDV);
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
return rowsAffected;
}

Thx

DateTime Menace

I have one table where I load the dates using datetime datatype. Now I need to copy only the month and year from tht and put it in another table as varchar, what will be the best way to strip the data........any exact query written will be great.

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 103),4,7) FROM TABLENAME

Thanks,

Loonysan

http://mystutter.blogspot.com

|||An excellent suggestion from Loonysan.

Also, note that you can get it in the format mm/yy by just changing the final value in the CONVERT function to 3 as below:

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 3),4,7)

If you want to put the month in one column and the year in another column you'll need to look at DATEPART

Code Snippet

SELECT DATEPART(Month, date), DATEPART(Year, date)

HTH!

|||

hey thanks guys.....the suggestions are really awesome, i will try al the three queries and will see which one is best for the my db.

|||

Hi,Chintan

here come another, just for you to ref. :=)

Select Convert(char(6),getdate(),112)

go

the result as below.

200708

(1 rows affected)

try it.

Best Regrads,

Hunt

|||

create procedure inmarine_Pre

as

truncate table herm_inmar_pre

insert into herm_inmar_pre

(

accountingdate,

transactioneffectivedate,

transactionexpirationdate,

statecode,

sublinecode,

classificationcode,

zipcode

)

select p.entrydate,

p.premiumeffectivedate,

p.policyexpirationdate,

p.statecode,

s.sublinecode,

s.classcode,

p.postalcode

from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s

on p.invoiceno = s.invoiceno and

s.lineofbusinesscode = '090'

and p.entrydate between '01/01/2007' and '12/31/2007'

order by p.entrydate asc

go

This is the procedure i am creating. But the problem here is, ENTRYDATE which is going in new table should have just one digit for month and one digit for year as stated below, day is not required

Jan to Sep is represented by 1 to 9

Oct with '0'

Nov with '-'

Dec with '&'

Year should be one digit for example if it is 2007 than it should be represented as 7 the values are from 2000 to 2007

p.entrydate is in datetime format and entrydate in herm_inmar_pre is VARCHAR(2)

|||

A bit of CASE should do the trick here:

declare @.entrydate datetime
set @.entrydate = '22 nov 2003'

SELECT (CASE DATEPART(M, @.entrydate)
WHEN 10 THEN '0'
WHEN 11 THEN '-'
WHEN 12 THEN '￡'
ELSE CAST(DATEPART(M, @.entrydate) AS CHAR(1))
END + LEFT(REVERSE(DATENAME(YY, @.entrydate)),1)) AS DateAbbrev

HTH!

DateTime Menace

I have one table where I load the dates using datetime datatype. Now I need to copy only the month and year from tht and put it in another table as varchar, what will be the best way to strip the data........any exact query written will be great.

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 103),4,7) FROM TABLENAME

Thanks,

Loonysan

http://mystutter.blogspot.com

|||An excellent suggestion from Loonysan.

Also, note that you can get it in the format mm/yy by just changing the final value in the CONVERT function to 3 as below:

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 3),4,7)

If you want to put the month in one column and the year in another column you'll need to look at DATEPART

Code Snippet

SELECT DATEPART(Month, date), DATEPART(Year, date)

HTH!

|||

hey thanks guys.....the suggestions are really awesome, i will try al the three queries and will see which one is best for the my db.

|||

Hi,Chintan

here come another, just for you to ref. :=)

Select Convert(char(6),getdate(),112)

go

the result as below.

200708

(1 rows affected)

try it.

Best Regrads,

Hunt

|||

create procedure inmarine_Pre

as

truncate table herm_inmar_pre

insert into herm_inmar_pre

(

accountingdate,

transactioneffectivedate,

transactionexpirationdate,

statecode,

sublinecode,

classificationcode,

zipcode

)

select p.entrydate,

p.premiumeffectivedate,

p.policyexpirationdate,

p.statecode,

s.sublinecode,

s.classcode,

p.postalcode

from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s

on p.invoiceno = s.invoiceno and

s.lineofbusinesscode = '090'

and p.entrydate between '01/01/2007' and '12/31/2007'

order by p.entrydate asc

go

This is the procedure i am creating. But the problem here is, ENTRYDATE which is going in new table should have just one digit for month and one digit for year as stated below, day is not required

Jan to Sep is represented by 1 to 9

Oct with '0'

Nov with '-'

Dec with '&'

Year should be one digit for example if it is 2007 than it should be represented as 7 the values are from 2000 to 2007

p.entrydate is in datetime format and entrydate in herm_inmar_pre is VARCHAR(2)

|||

A bit of CASE should do the trick here:

declare @.entrydate datetime
set @.entrydate = '22 nov 2003'

SELECT (CASE DATEPART(M, @.entrydate)
WHEN 10 THEN '0'
WHEN 11 THEN '-'
WHEN 12 THEN '￡'
ELSE CAST(DATEPART(M, @.entrydate) AS CHAR(1))
END + LEFT(REVERSE(DATENAME(YY, @.entrydate)),1)) AS DateAbbrev

HTH!

datetime function with no time component?

Hi All,

When I compare dates but I want to ignore the time within the datetime I find myself doing this:

CONVERT(int, CONVERT(char(8), @.MyDate, 112))

style 112 is yyyymmdd

int is very predictable for comparisons, and performs well too.

It works but it is not readable, especially if you have several of these expressions in the same WHERE clause or CASE stmt. I also tried a udf but that has its own reusability problems across dbs and projects.

Is there a cleaner way to do this with a system function?

Carl

If you just want to compare dates, ignoring times, you could use the datediff function:

WHERE datediff( day, MyFirstDate, MyOtherDateTime ) = 0

For example:

Code Snippet

SELECT
Match = CASE
WHEN datediff( day, '2007/07/07 08:45 AM', getdate() ) = 0
THEN 'Match -Same Day'
ELSE 'Bummer! -No Match'
END,
NoMatch = CASE
WHEN datediff( day, '2007/07/06 08:45 AM', getdate() ) = 0
THEN 'Same Day'
ELSE 'Different Day'
END

Match NoMatch
-- -
Match -Same Day Different Day

DATEDIFF(), using the 'day' parameter, verifies that the two values are the same date IF there is NO difference [ = 0 ].

|||

Thanks Arnie,

For = and != logic, this is cleaner.

Not much of an improvement in readability for >, < , !>, and !< type comparisons

Carl

|||

And not too good for performance either.

While using the datediff() process 'looks' good, or as you said, 'cleaner', performance, related to other methods, can be disasterous. It will require at 'best', a clustered index scan. Actually, unless there is an index on the datetime column, it has to scan the entire table -which is what a 'clustered index scan' really is.

Compare that with the second option, my preferred method, of using date values in the criteria.

Code Snippet

USE Northwind
GO

SELECT *
FROM Orders
WHERE datediff( day, OrderDate, '1996/08/27' ) = 0

SELECT *
FROM Orders
WHERE ( OrderDate >= '1996/08/27'
AND OrderDate < '1996/08/28'
)

If you examine the execution plans, you will notice the method using the datediff() takes 19 times as long to execute since it has to scan the entire table.

DateTime Format problem

In SQL query I have to find records which occour between two dates. I created Select query with two parameters @.date1 and @.date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.

Use the Convert Function to convert it to a small date it will trim the time part

Where Convert(Varchar(10),@.Date1) = Convert(Varchar(10),@.Date2) ... Also you can use the third parameter in the Convert Function to get a specific format of dates i.e dd/mm/yyyy or yyyy/mm/dd etc. For a complete list

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Check the link

|||

If the goal is to retrieve data for a single day, the method I prefer is lower inclusion, upper exclusion. Let me explain:

declare @.dtdatetime, @.startDatedatetime, @.endDatedatetime-- assume this is the dateset @.dt ='2007-01-02 12:34:56'select-- if only a date portion is passed into the sproc -- you won't need to remove the time portion @.startDate =convert(char(10), @.dt, 120) , @.endDate =dateadd(day, 1, @.startDate)select a.*-- use column list here!from tbl awhere-- inclusive of the lower limit a.DateColumn >= @.startDate-- exclusive of the upper limitand a.DateColumn < @.endDate

DateTime Format in spanish

Hello, I have a report that shows some dates, I put in Format Code D,

but it shows me , Thuersday, June 14 of 2006, I need it in spanish and more personalized for example only the month and year,

JUNIO DE 2006

you could define your own format string..
try:
dd.MM.yyyy HH:mm (wich is the german format) and modify it to your needs..
|||

what about the language? it depends on the collation settings of the database?

There is something strange, my windows 2003 is in spanish and I have the media of sql developer in english, when I installed reporting services manager is in spanish.

So what should I do to see the dates in spanish?

|||

I solvd it changing the report language very, easy but I havent been able to do this

=MonthName(First(Fields!feinicio.Value, "DSResultadosRequisitos"), false).ToString() & " " & Year(First(Fields!feinicio.Value, "DSResultadosRequisitos").ToString())

Do you see any errors?

datetime format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

DateTime data type and 12:00am

Hi guys.

I have a datetime data type column set up for keeping dates. 12:00am would like to hang around when I don't want it too. I don't know if the best solution is to have the server format it for me or if that should be done on the client side. In either case I need a little guidance. My app is a simple blog program that uses a dataset to populate a datalist from mssql 2005. I've looked through some of the other entries that people have posted but I'm to new to this particular issue and sql to transcribe their issue's fix to mine... at least from the enties I've read so far. hence my requst for help! Your assistance is greatly appreciated.

Mucho thanks.

Fatthippo.

You can format the datetime either way, but it would be better by doing it from client side. For example,

<ItemTemplate>dob:<asp:Label ID="dobLabel" runat="server" Text='<%# Bind("dob","{0:MM/dd/yyyy}")%>'></asp:Label></ItemTemplate>

|||

Thankyou limno!

that satifies the questions but if I'm always formatting what comes out of mssql and not what's going in, will that hinder any search querries I might want to do in the future if 12:00am is always at the end? In otherwords, is there a benfit or downside to using the technique in the above example?

Thanks again!

Fatthippo

|||

Datetime data type has two parts date and time. It should be a good practice to use datetime this way instead of as a string type. From my limit knowledge, we should choose to do this sort of formating from client side to save a little bit extral calculation on database engine. There are other ways to format date time to fit your need. You can look it up depending on what kind of control you are using. When I am working on my projects, I use them interchangably in light load applications. But without further testing, I cannot give you any firm recomendation on this. You can search for this information from various forums and I am sure you will get a lot of information on this. I like to play with formating datetime in SQL to learn.

datetime Data Type

I am trying to insert dates and times into a SQL database using a small ASP
application I have just written to test it.
The dates are being passed in format: dd/mm/yyyy, and the times in format:
hh:mm:ss
However, when I set the fields as datatype datetime, it fails saying:
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
What am I doing wrong? If I change the datatype of the field to char it
works fine, but I wanted them as datetime.
What do I need to change?
ThanksIf you want to that format, you need to have proper SET DATEFORMAT setting.
I suggest you read below article, and use a language neutral format.
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <@..> wrote in message news:OFPuQX6CEHA.3280@.TK2MSFTNGP09.phx.gbl...
> I am trying to insert dates and times into a SQL database using a small
ASP
> application I have just written to test it.
> The dates are being passed in format: dd/mm/yyyy, and the times in format:
> hh:mm:ss
> However, when I set the fields as datatype datetime, it fails saying:
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> What am I doing wrong? If I change the datatype of the field to char it
> works fine, but I wanted them as datetime.
> What do I need to change?
> Thanks
>

datetime convert grief

Hi, I have a problem when I traverse a table to update another, I have a nvarchar in the first holding some dates - (the only option). Though these need to be converted when the records are copied and updated. This is my code that is returning "Syntax error converting datetime from character string." Is there another way that i could do this ?

Declare @.CardNumber int
Declare @.EmployeeNumber int
Declare @.DutyDate nVarchar
Declare @.StartTime nvarchar
Declare @.DutyConvert datetime
Declare @.StartConvert datetime

Declare rsMyCursor Cursor For Select [Site Card Number],[Employee Number],[Duty Date],[Start Time] FROM tblRosta1 WHERE checking is null
Open rsMyCursor

Fetch Next From rsMyCursor
INTO @.CardNumber, @.EmployeeNumber, @.DutyDate, @.StartTime

While @.@.Fetch_Status = 0

Begin
--Select @.DutyConvert = Convert(datetime, @.DutyDate)
--Select @.StartConvert = Convert(datetime, @.StartTime)

INSERT INTO [tblDuties Repository] ([Site Card Number],[Employee Number], [Duty Date], [Start Time]) Values (@.CardNumber, @.EmployeeNumber, Convert(datetime, @.DutyDate), Convert(datetime, @.StartTime))

print + @.CardNumber
Fetch Next From rsMyCursor
INTO @.CardNumber, @.EmployeeNumber, @.DutyDate, @.StartTime

End

Close rsMyCursor
Deallocate rsMyCursor

Any help would be great.

RingoHi

In the variable declaration mention the size. Secondly give a select from that table for these varchar fields and check whether any non-date values are present.

\joe

Datetime conversion under diferent versions of SQL

You 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/info_datetime.asp
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 message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You 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...
>