SOLVED: Passing Excel (2007) date "mmm yyyy" to SP

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

Other SQL Server Topics (2005)

SOLVED: Passing Excel (2007) date "mmm yyyy" to SP

Author

Topic

borntorun75
Starting Member

4 Posts

Posted - 2010-07-12 : 05:37:37

Hi there,

I'm after help on an Excel 2007 -> MSSQL parameterised 2005 Stored Procedure.

I have a date cell in Excel, which is actually formatted as mmm yyyy. I've deliberately dropped the dd out of the format because from a user experience, they don't need to worry about the dd part of it. However, as a value it really is a 'proper' date, and is 'required'.

I want to pass that date value into an MSSQL 2005 Stored Procedure.

Even though it is formatted as mmm yyyy in Excel, I need to pass the proper full date value into an MSSQL 2005 SP.

It is not being passed by VBA. It's going through a data connection string - exec spGetData ?

I cannot tell. Would the Stored Procedure receive that parameter as a date field (i.e. its native format), or as the formatted date ?

Cell A1 value : 25/12/2010
Displayed value : December 2010

So, is the SP receiving 25/12/2010 or December 2010 ? Ideally I'd want the actual date value (25/12/2010) being passed into the SP so I don't have to do any conversion at the SP end.

The SP itself, I want to SELECT x where data_date = @input_date

The interaction between Excel 2007 and SQL is actually very nice, but this is the final issue I'm trying to resolve.

Thanks for any help you're able to give. Much appreciated.

borntorun75
Starting Member

4 Posts

Posted - 2010-07-12 : 09:24:30

Hi,

Have managed to solve this one myself. Excel does, as I'd hoped, pass through the raw Excel data through to the stored procedure.

i.e. Even though I've got the cell formatted as 'mmm yyyy', it passes the date in its native, non-formatted value.

So .... The SP is like this ....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spGetData] @input_date smalldatetime

AS
BEGIN

select col1, col2 from tbl_data where data_date = convert(smalldatetime,@input_date,103)

END

The cell in Excel is an actual date (25/12/2010), but formatted as 'December 2010'. The cell is actually a drop down list, itself populated from unique values taken from that very same MSSQL tbl_data table.

Nice and neat :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources