how to translate datetime to european standard

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

All Forums

General SQL Server Forums

New to SQL Server Programming

how to translate datetime to european standard

Author

Topic

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 06:25:12

hi

I have this stored procedure, but the SQL do not translate the datetime to european standard.

I get problem if I use 13-12-2011, I get this error:

"Error converting data type nvarchar to datetime."

If I write 12-12-2011 it is succesfully, so I have a transfer problem, but where can I place the CONVERT in this stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spAdd_Exists]
(
@Name nvarchar(50),
@Booked nvarchar(600),
@Start datetime,
@End datetime
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT
NULL
FROM
Table WITH (UPDLOCK)
WHERE
Name = @Navn AND
Booked = @Booked

)
BEGIN
SELECT @Result = -1
END
ELSE
BEGIN
INSERT INTO
Table
(
Name,
Booked,
Start,
End
)
VALUES
(
@Name,
@Booked,
@Start,
@End
)
SELECT @Result = @@ERROR
END
IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:28:39

pass dates in universal unambiguos format yyyymmdd and there will be no issues

ie.20111213,20111212, etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:29:58

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 06:30:19

INSERT INTO
Table
(
Name,
Booked,
Start,
End
)
select
(
@Name,
@Booked,
convert(datetime, @Start, 105),
convert(datetime, @End, 105)

yyyymmdd and yyyy-mm-ddThh:mm:ss.mmm are reliably converted without a style

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 06:38:27

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't understand what you are saying, can you please try to explain a lite more detailed ?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 06:47:58

You have the options of changing the format that is sent to your sp, reformatting in the sp or handling the format that is sent.
The reformatting could be to yyyymmdd or yyyy-mm-ddThh:mm:ss.mmm or the style for the existing format could be 105.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 06:48:58

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I did not help me, still the same, if I type 13-12-2009 I get error, and if I type 12-12-2009 it is succesfully

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:49:29

quote:
Originally posted by jylland

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't understand what you are saying, can you please try to explain a lite more detailed ?

Thanks

where are you calling the sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 06:50:32

quote:
Originally posted by jylland

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I did not help me, still the same, if I type 13-12-2009 I get error, and if I type 12-12-2009 it is succesfully

The suggestion was to pass it as 20111213

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 07:00:01

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't understand what you are saying, can you please try to explain a lite more detailed ?

Thanks

where are you calling the sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In the SQL server, but it should also be called from an asp.net site

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 07:02:20

quote:
Originally posted by visakh16

quote:
Originally posted by jylland

quote:
Originally posted by visakh16

and if by any chance values are coming from an application in your format make sure you apply format functions at front end and make it yyyymmdd before passing it to procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I did not help me, still the same, if I type 13-12-2009 I get error, and if I type 12-12-2009 it is succesfully

The suggestion was to pass it as 20111213

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No thats not good, it should be called from an asp.net site, here in europe we do not type it as 20111213

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:04:19

ok.in sql server if you're calling directly pass as 20111213

if passing from asp.net site. apply format function in .net to change it to yyyymmdd format fro whatever format its coming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 07:06:00

quote:
Originally posted by visakh16

ok.in sql server if you're calling directly pass as 20111213

if passing from asp.net site. apply format function in .net to change it to yyyymmdd format fro whatever format its coming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Okay I will try this, and will went back here later to tell my result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:13:51

ok...thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jylland
Starting Member

27 Posts

Posted - 2011-10-07 : 07:45:53

quote:
Originally posted by visakh16

ok...thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Okay that was the solution I transfer the 24-12-2011 to 12-24-2011 in the asp.net site which is send to the stored procedure which again transfer (why it did I don't understand) it back to 24-12-2011

a litle crazy but well It do well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:48:45

why convert again to ambiguos format. why not convert it to 20111224 which will work in all servers irrespective of language and regional settings. the format used even now still makes problems if server language setting is something other than us english

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources