Get date from db into MS Query

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

Transact-SQL (2008)

Get date from db into MS Query

Author

Topic

althea
Starting Member

2 Posts

Posted - 2011-02-14 : 11:33:18

I have a statement below in MS Query which is taking data from my sql 2008 database and displaying it in Excel. I'd like the date format to be mm/dd/yyyy in my excel file instead of the way it is in the database, which is varchar(10)--yyyymmdd. As you can see below, I'm only displaying the dates based upon dates user enters.

SELECT DailyDeposit.PrimarySiteId, DailyDeposit.ClosingDate, DailyDeposit.DepositAmount
FROM SupportDB.dbo.DailyDeposit DailyDeposit
WHERE (DailyDeposit.ClosingDate>=? And DailyDeposit.ClosingDate<=?)
ORDER BY DailyDeposit.PrimarySiteId, DailyDeposit.ClosingDate

Thanks so much!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 11:35:52

You have to do that formatting in Excel, no matter what format you take it of SQL in. Is the closingDate a varchar data type or a date data type?

Jim

Everyday I learn something that somebody else already knew

javad_ghasemiro
Starting Member

5 Posts

Posted - 2011-02-14 : 11:54:42

Use convert function to avoid these problems

example
adate >= Convert (datetime , '2011/02/15' , 102 )
or
adate >= Convert (datetime , @aStringParameter , 102 )

third parameter (102) is method of parsing string

Javad Ghasemi

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 12:16:58

quote:

of the way it is in the database, which is varchar(10)--yyyymmdd

Why are you database fields for dates stored as 10 digit varchars? That seems bound for issues if there are any invalid dates as the query may run into conversion errors. Displaying the format in excel is ideal but isn't possible with the 8 character UTC string unless you send them to excel as a date.

To query the database which has strings, you need to convert your parameters to match the database format first provided the ? implies a parameter OR the ? is formatted as a date filter in MSQuery syntax of #mm/dd/yyyy#

= CONVERT(varchar(8),?,112) will convert a datetime value to a YYYYMMDD string.

As far as the ? in your sample. Are those parameters fed by an excel cell for a parameterized query?

Poor planning on your part does not constitute an emergency on my part.

althea
Starting Member

2 Posts

Posted - 2011-02-14 : 15:40:15

Thanks so much for all the help so far. To answer the question 'As far as the ? in your sample. Are those parameters fed by an excel cell for a parameterized query?'

I've tried it both ways. Currently I'd like to feed it from two excel cells. one is 'beginning date' and one is 'ending date'.

ClosingDate is varchar type.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 16:10:22

If ClosingDate is varchar, what format are the excel cells..are they strings like the source data? or are the date values in excel?

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources