Date conversion problem! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Date conversion problem!

Author

Topic

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 08:12:31

Hello,

If I insert a date lets say 1/2/2001 It stores it like: 2/1/2001
My SP is as next (the datum field).
I know I have to use a convert... can someone help me out?

SP:

CREATE PROCEDURE spActiviteitToevoegen

(@FirmaID bigint,
@ContactID bigint,
@SoortenactiviteitID int,
@OfferteID bigint,
@OpportunityID bigint,
@MedewerkerID int,
@Datum datetime,

bla bla

AS

DECLARE @ActiviteitID bigint

BEGIN

INSERT INTO Activiteiten(FirmaID, ContactID, SoortenactiviteitID, OfferteID, OpportunityID, MedewekerID, Datum, Onderwerp, ActiviteitstatusID,Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES(@FirmaID, @ContactID,@SoortenactiviteitID,@OfferteID,@OpportunityID,@MedewerkerID, @Datum, @Onderwerp, @ActiviteitstatusID,@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

SELECT @ActiviteitID = @@identity

END

INSERT INTO Activiteitdetail(ActiviteitID, ArtikelID, Opmerkingen,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES (@ActiviteitID, @ArtikelID, @Opmerkingen,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

NSERT INTO Bezoekrapporten(ActiviteitID, Segment, Data,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES ( @ActiviteitID, @Segment, @Data,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

RETURN @ActiviteitID
GO

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 08:32:47

The crazy thing is that when I insert 17/1/2002 it works fine.
But if I use <13 mm then it switches the day and the month?
What the hell is happening here???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 08:36:21

SQL Server is made by a U.S. company and will interpret date values in US format. If you have an ambiguous date like 11/01/2002, it will interpret it as November 1, 2002 instead of January 11, 2002. Even if you use SET DATEFORMAT, you still risk having the date interpreted incorrectly.

The best way to pass date values is to use the ISO YYYYMMDD format, it is ALWAYS interpreted correctly (20021101 is November 1, 20020111 is January 11)

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 08:40:10

Thnx ROBVOLK,

But I figured it out!

I used this in my code and works very fine:

VALUES(@FirmaID, @ContactID,@SoortenactiviteitID,@OfferteID,@OpportunityID,@MedewerkerID, !!!!!!!!!! Convert(varchar, @Datum, 101) !!!!!!!!!

Thnx all!

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 08:52:07

I'm taking my words back!

It works fine for everything < 13. But if I want to insert > 12 Then it gives again the same conversion error!

So your help is still needed.

THNX all

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 09:03:03

USE THE ISO YYYYMMDD DATE FORMAT!

Nazim
A custom title

1408 Posts

Posted - 2002-04-18 : 09:13:00

or use to ISO style format

Convert(varchar, @Datum, 112)

--------------------------------------------------------------

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 09:26:35

NOPE it still change the place of the day and the month!

What a iritating problem is this!

HAHA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 09:29:30

Incognito-

The YYYYMMDD format ALWAYS, ALWAYS, ALWAYS works. Period. ALWAYS. If you are having ANY troubles with date interpretation, use the ISO format and they will go away.

Incognito
Starting Member

49 Posts

Posted - 2002-04-18 : 09:34:49

OK OK DONT SCREAM!!!

I used the ISO style format but my users want to use the DUTCH notation.
So I used for my SP the ""Convert(varchar, @Datum, 112)""
But If I want to insert into the SQL SERVER 2000 via ASP it changes
the day and the month place. That is what I dont understand. I am also using LCID = 1043 which is DUTCH. But IT IT IT IT DOESNT WORK!
Thats the problem.

Thnx!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-18 : 10:10:02

I would recommend that you set up separate input boxes for entering the month, date, and year, instead of allowing the user to enter it in DD/MM/YYYY format, and then combining them into YYYYMMDD format. Unfortunately, MS products have this predeliction for treating dates as US format first, European format last, and the only reliable way to get around it is to use ISO format.

You're not alone with this frustration, that's why you see MANY MANY large corporate web sites that have drop-down select boxes for month and date (and even year)

Edited by - robvolk on 04/18/2002 10:11:05

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-04-18 : 17:08:46

This is as much a UI issue as it is a database one.

I guarantee you that if all us SQL heads plus Microsoft combined still have trouble wrangling dates, your users will do. Even if you get the insert working the way you want, some user is going to get confused, enter the date in US format (who knows, maybe they used to live here, or the last app they used required it, or...) and then you're going to get support calls about the date field not working right.

robvolk is absolutely right: the only way to ensure mostly error-free data entry is to use discrete day, month, and year fields on the UI, and the ISO format when talking to SQL server.

Cheers
-b

Incognito
Starting Member

49 Posts

Posted - 2002-04-19 : 05:31:53

Hello: to prevent pollution I go on, on this topic!

I have now 3 listmenu's dag(day) maand(month) jaar(year DUTCH) wich
I combine in the ISO standard: yyyymmdd

I followed your advice and this is how I try to solve it:
Look at @datum!!!
I get this error and need again your help to fix it:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int.
/msis/Activiteiten/invoeren/do_invoeren1.asp, line 110

Thnx for suggestions,

GIA

CODE:

CREATE PROCEDURE spActiviteitToevoegen1

(@FirmaID bigint,
@ContactID bigint,
@SoortenactiviteitID int,
@OfferteID bigint,
@OpportunityID bigint,
@MedewerkerID int,
@Dag varchar(50),
@Maand varchar(50),
@Jaar varchar(50),
@Datum nvarchar,
@Onderwerp nvarchar(50),
@ActiviteitstatusID int,
@ArtikelID int,
@Opmerkingen nvarchar(1000),
@Gewijzigddoor nvarchar(50),
@Laatstewijziging datetime,
@SysteemStatus nvarchar(50),
@IP nvarchar(50),
@Segment int,
@Data varchar(8000))

AS

DECLARE @ActiviteitID bigint

BEGIN

!!!!!!!!!!Select @Datum = @Jaar+'/'+@Maand+'/'+@Dag!!!!!!!!!!!!!!

INSERT INTO Activiteiten(FirmaID, ContactID, SoortenactiviteitID, OfferteID, OpportunityID, MedewekerID, Datum, Onderwerp, ActiviteitstatusID,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES(@FirmaID, @ContactID,@SoortenactiviteitID,@OfferteID,@OpportunityID,@MedewerkerID, @Datum, @Onderwerp, @ActiviteitstatusID,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

SELECT @ActiviteitID = @@identity
END

INSERT INTO Activiteitdetail(ActiviteitID, ArtikelID, Opmerkingen,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES (@ActiviteitID, @ArtikelID, @Opmerkingen,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

INSERT INTO Bezoekrapporten(ActiviteitID, Segment, Data,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES ( @ActiviteitID, @Segment, @Data,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

RETURN @ActiviteitID
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-19 : 07:41:32

That's easy to fix:

Select @Datum = Str(@Jaar, 4, 0) + '/' + Replace(Str(@Maand, 2, 0), ' ', '0') + '/' + Replace(Str(@Dag, 2, 0), ' ', '0')

Str() converts a number to a string, and it pads the number with spaces; using Replace() changes the spaces to zeros.

vgshanky
Starting Member

1 Post

Posted - 2002-04-22 : 02:55:27

The Best way to Solve this problem is simple. before executing ur insert statement please execute this "SET DATEFORMAT DMY" or if u dont like this then u can use the convert(char(10),datefield,103)
or YYYYMMDD. this is what I found after almost 4Yrs of experience.
--Shanky

Hello,

If I insert a date lets say 1/2/2001 It stores it like: 2/1/2001
My SP is as next (the datum field).
I know I have to use a convert... can someone help me out?

SP:

CREATE PROCEDURE spActiviteitToevoegen

(@FirmaID bigint,
@ContactID bigint,
@SoortenactiviteitID int,
@OfferteID bigint,
@OpportunityID bigint,
@MedewerkerID int,
@Datum datetime,

bla bla

AS

DECLARE @ActiviteitID bigint

BEGIN

INSERT INTO Activiteiten(FirmaID, ContactID, SoortenactiviteitID, OfferteID, OpportunityID, MedewekerID, Datum, Onderwerp, ActiviteitstatusID,Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES(@FirmaID, @ContactID,@SoortenactiviteitID,@OfferteID,@OpportunityID,@MedewerkerID, @Datum, @Onderwerp, @ActiviteitstatusID,@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

SELECT @ActiviteitID = @@identity

END

INSERT INTO Activiteitdetail(ActiviteitID, ArtikelID, Opmerkingen,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES (@ActiviteitID, @ArtikelID, @Opmerkingen,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

NSERT INTO Bezoekrapporten(ActiviteitID, Segment, Data,
Gewijzigddoor, Laatstewijziging, SysteemStatus, IP)

VALUES ( @ActiviteitID, @Segment, @Data,
@Gewijzigddoor, @Laatstewijziging, @SysteemStatus, @IP)

RETURN @ActiviteitID
GO

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources