DateTime Format - 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

Transact-SQL (2000)

DateTime Format

Author

Topic

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 01:11:39

I have a function that is based aounr the input of parameters. The last remaing issue is that I am required to enter the data into the parameter field as mm/dd/yyyy. I want to be able to enter the data as dd/mm/yyyy. I have tried to use

WHERE (CONVERT(datetime,src_terrier.datadate,103) = @dt_src_date) AND..........

But this just throws an error "Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0
Error converting data type nvarchar to datetime."

The execution line I am using is

USE [DashboardSQL-2K5]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'28/04/2006',
@chr_div = NULL,
@vch_portfolio_no = NULL,
@vch_prop_cat = NULL

SELECT 'Return Value' = @return_value

GO
Anybody got any ideas as to what I have done wrong? I have also tried it without the N just before the date and get a varchar version of the same error.

Thanks in advance

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-14 : 01:15:04

Pass in the data time string in universal YYYYMMDD format.

when you pass in as '28/04/2006' depending on your locale, it might be interpret as MM/DD/YYYY which will cause the conversion error when it is trying to convert to datetime data type.

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 01:22:34

Morning khtan,

Thanks for your quick response. How do I pass it in universal format as you have suggested. Am I on the right lines with the code I am using or am I way off base?

Regards

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-14 : 01:30:06

Your stored procedure must have declared the input parameter @dt_src_date as datetime data type. To avoid confusion of DDMMYYYY or MMDDYYYY, you should pass in date in with YYYYMMDD format

Try this

EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'20060428',

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 01:36:41

Hi

You are correct in saying that I have declared the value as datetime at the top of the function. I tried the code you sggested and it returned the same data as when I enter the value of mm/dd/yyyy.

Are you saying that I should declare the parameter type as something different.......varchar for arguments sake?

Regards

Toni C
aka Talisa

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-14 : 02:00:50

When ever you are passing the date to the store procedure pass them in YYYYMMDD format
since this format does not rely on the local settings and its the universal date format

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 02:08:10

OK, I think I understand what you are saying, however the people that will be using the report are very set on selecting data in reporting services with the dd/mm/yyyy format. Is this something that I can change from a visual point of view in RS. Perhaps on the query that shows the dates availabel to the user, the visual elemet of it only could be dd/mm/yyyy, but when the data is passed it is just passed in default mm/dd/yyyy format behind the scenes.

Does that make any sense?

Regards

Toni C
aka Talisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-14 : 02:13:37

Yes. You can format the datetime in dd/mm/yyyy or whatever the user desired when you shows the datetime on the report. And when passing datetime to the stored procedure, you can use YYYYMMDD or if the date is input by user in dd/mm/yyyy format you can convert the user input of dd/mm/yyyy to datetime and pass it to stored procedure.

convert(datetime, @date, 103) will convert @date in DD/MM/YYYY string format to datetime datatype

Kristen
Test

22859 Posts

Posted - 2006-05-14 : 02:14:10

Might be better to change:

WHERE (CONVERT(datetime,src_terrier.datadate,103) = @dt_src_date) AND..........

to

WHERE (src_terrier.datadate = CONVERT(datetime, @dt_src_date, 103)) AND..........

because if src_terrier.datadate is indexed then in the first instance the index won't be used, whereas in the second instance it will.

Note that this will raise an error if the user submits an invalid date

"@dt_src_date = N'28/04/2006',"

No point using Nvarchar - it will just waste bytes! (make sure @dt_src_date is declared as Varchar too)

Kristen

Tonic
Starting Member

26 Posts

Posted - 2006-05-14 : 02:19:27

Thank you to you both. I will go play with that now.

Kindest Regards

Toni C
aka Talisa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources