How to pass delimited dates to a procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to pass delimited dates to a procedure?

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-13 : 11:16:55

I need to pass multiple date values (up to 200) from ASP.NET to SQL 2005.
Previously I have simply passed comma separated values for other data types, such as INT, like this...


-- ASP
Dim userIDs as String = "23,44,56,78,454,554,667,669,"

-- SQL
DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)
DECLARE @userTable TABLE(userID INT)
WHILE patindex('%' + @separator + '%' , @userIDs) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @userIDs)
		SELECT @array_value = LEFT(@userIDs, @separator_position - 1)
		INSERT INTO @userTable (userID) VALUES (@array_value)
		SELECT @userIDs = stuff(@userIDs, 1, @separator_position, '')
	END

With date values, is there any chance that using commas might affect the data, e.g. corrupt it? My application is multi-cultural, so, is there a safe-delimiter character I can use? Alternatively, is there a better approach altogether?

Thanks.

CSears
Starting Member

39 Posts

Posted - 2010-09-13 : 11:21:29

the | delimiter is typically safe all over. As far as DataTime Values, since you are just putting them into a table, you can just pass them in the same format that you would assign them in code.

2010-09-13 11:24:00.000|2010-09-13 11:24:00.000

If you have the column type as DateTime, SQL should convert the string automatically for you. I am not sure what other string formats are acceptable for this approach tho.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-13 : 11:23:00

you can pass a table variable as a parameter in 2008. Upgrade maybe?

There's a good article on arrays here:
http://www.sommarskog.se/arrays-in-sql.html

IF you do use an array I'd go for ISO standard dates and use a comma as a seperator.

Either

YYYYMMDD
or
YYYY-MM-DDTHH:MM:SS.MSS

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-13 : 12:15:36

quote:
you can pass a table variable as a parameter in 2008. Upgrade maybe?

Music to my ears! Although sadly we're stuck with 2005 for the foreseeable future.

quote:
If you do use an array I'd go for ISO standard dates and use a comma as a seperator.

I'm not sure of the underlying date format, as ASP.NET automatically shows it using the user's locale (culture settings). I would imagine that Microsoft would be using the ISO format though

quote:
the | delimiter is typically safe all over. As far as DataTime Values, since you are just putting them into a table, you can just pass them in the same format that you would assign them in code.
2010-09-13 11:24:00.000|2010-09-13 11:24:00.000

Ahh okay. Well, I actually use the Pipe character to separate the values (thought it was comma originally until I checked

). I'll give the pipe approach a try and see what happens. I'm using CONVERT within the chunk of T-SQL that reads the values from the nvarchar paramter, so it should work...

Kristen
Test

22859 Posts

Posted - 2010-09-13 : 14:52:35

Get rid of the hyphens from the dates,and use YYYYMMDD format, which SQL will treat unambiguously as TC has said. If you need Time too then you need the hyphens (but year first - yyyy-mm-dd) and you need the "T" rather than a space separating the time.

Anything else comes with risk that SQL will make a wrong "ambiguity" assumption unless you use an Explicit CONVERT with Style indicator, or a SET DATEFORMAT dmy directive as date parsing is based on Server settings, User Language or Country, and a variety of other bits of Smoke and Mirrors that may work just fine in DEV, and even for Years in Production, and then bite you from behind when you move to a new server or make some other seemingly minor change. Pity they ever built in so much flexibility such that loads of applications have been built with weak date parsing assumptions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:42:20

Also make sure to read this post too
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-14 : 06:32:00

That's great advice. Thanks everyone.

It should be pretty straightforward in ASP.NET to manipulate the dates to read YYYYMMDD (as I don't require the TIME part).

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-14 : 11:19:49

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-22 : 04:17:11

quote:
Originally posted by madhivanan

Also make sure to read this post too
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

quote:
Originally posted by Kristen

Get rid of the hyphens from the dates,and use YYYYMMDD format, which SQL will treat unambiguously as TC has said. If you need Time too then you need the hyphens (but year first - yyyy-mm-dd) and you need the "T" rather than a space separating the time.

Thank you for these very useful pointers. I followed your advice, and got my ASP.NET application to rewrite the date value into this format. I haven't yet tried this out, but say all the dates are 29 March 2010. My app turns these into Universal Coordinated Time, so they become 28 March 2010 23:00:00. The app is then passing these as a long string to SQL like this:

2010-3-28T23:0:0|2010-3-28T23:0:0|2010-3-28T23:0:0|

Does it matter that the month, hour and minute values are only a single integer value (e.g. 3 instead of 03)?

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 04:28:04

Yup, 'fraid so.

SELECT CONVERT(datetime, '2010-3-28T23:0:0')

Server: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT CONVERT(datetime, '2010-03-28T23:00:00')

2010-03-28 23:00:00.000

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-22 : 04:45:55

Hi Kristen

Not a problem. I've just stumbled upon .NET's .ToString("D2") command that pads the integer values with an extra zero when required, which is very useful.

Thanks very much for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources