Force to date format MMDDYY

My sql :

SELECT....., date_ of_ shipment,.....

FROM......

WHERE.....

ORDER BY....

After that it will export to excel.

My problem: Some times I get a column of mixing date format MMDDYY and number.?

I have to format the whole excel column to get all date format.

My question: How can I write sql so it force the the result to be date format MMDDYY in excel? Where this clause in sql (where clause ?)

Thanks
Daniel

Code Snippet

select convert(varchar(8), getdate(), 1)

OR

select replace(convert(varchar(8), getdate(), 1), '/', '')

Depending on whether you want the slash separators or not.

Use code 101 instead of 1 in the Convert if you want 4 digit years.

|||

If my column name is date_of _shipment

Select convert (varchar(8), date_of_shipment,1) ?

Thnks

Daniel

|||

Right.

It will also lose it's name, so you may want to re-alias it.

Code Snippet

Select convert (varchar(8), date_of_shipment,1) AS date_of_shipment

|||

Thanks a millions. Look like it work great for what ever my excel column format.

Daniel

|||

I may need hour: min: second too

How about MMDDYYYYHHMMSS ?

Thanks
Daniel

FOR XML: date returned without timezone

I'm querying a table with a datetime field using SELECT ... FOR XML. The
dates are returned in the following format:
<updated>2006-01-04T11:52:52.403</updated>
Is there any possiblity to add the timezone information to it? The result
should look as follows:
<updated>2006-01-04T11:52:52.403Z</updated>
The SQL statements looks like
SELECT date AS updated
FROM articles FOR XML PATH('entry')
Best regards
MartinI found a solution:
CONVERT(VARCHAR, date, 126) + 'Z' AS updated
126 is the style code for the ISO8601 date representation, i.e. XML
representation.
"Martin Szugat" wrote:

> I'm querying a table with a datetime field using SELECT ... FOR XML. The
> dates are returned in the following format:
> <updated>2006-01-04T11:52:52.403</updated>
> Is there any possiblity to add the timezone information to it? The result
> should look as follows:
> <updated>2006-01-04T11:52:52.403Z</updated>
> The SQL statements looks like
> SELECT date AS updated
> FROM articles FOR XML PATH('entry')
> Best regards
> Martin

FOR XML: date returned without timezone

I'm querying a table with a datetime field using SELECT ... FOR XML. The
dates are returned in the following format:
<updated>2006-01-04T11:52:52.403</updated>
Is there any possiblity to add the timezone information to it? The result
should look as follows:
<updated>2006-01-04T11:52:52.403Z</updated>
The SQL statements looks like
SELECT date AS updated
FROM articles FOR XML PATH('entry')
Best regards
Martin
I found a solution:
CONVERT(VARCHAR, date, 126) + 'Z' AS updated
126 is the style code for the ISO8601 date representation, i.e. XML
representation.
"Martin Szugat" wrote:

> I'm querying a table with a datetime field using SELECT ... FOR XML. The
> dates are returned in the following format:
> <updated>2006-01-04T11:52:52.403</updated>
> Is there any possiblity to add the timezone information to it? The result
> should look as follows:
> <updated>2006-01-04T11:52:52.403Z</updated>
> The SQL statements looks like
> SELECT date AS updated
> FROM articles FOR XML PATH('entry')
> Best regards
> Martin

For Loop Container with Dates

I'm trying to set up a For Loop Container that steps through the loop based on date logic.

Here's an example of what I'd like to do:

InitExpression | @.SomeDate = 1/1/2007

EvalExpression | @.SomeDate < GETDATE()

AssignExpression | @.SomeDate = DATEADD(DAY, 1, @.SomeDate)

The above syntax does not work, and I can't find an example that uses date logic with the For Loop Container.

Can anyone help me with this, or point me to an example that uses the For Loop Container with dats?

Thanks in advance for the help!

Is @.SomeDate an SSIS variable of DateTime type? Make it so.

Otherwise it looks fine. I tried this expression as my AssignExpression and it seemed to work:
@.SomeDate = dateadd("d",1,@.SomeDate)

Are you getting any errors that you care to share with us? That usually helps the most.|||

Thanks for the prompt reply!

I think this is my problem. Here's the message I get:

Error at SomeDate Loop: The data types "DT_DATE" and "DT_I4" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Where would I configure the data type for @.SomeDate? I created a package variable called SomeDate and set the data type there. I guess there is not a direct link between these variable?

Thanks again for the help!

|||Yep, you have that right, but you are missing quotes.

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)|||

Phil Brammer wrote:

Yep, you have that right, but you are missing quotes.

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)

I guess it's not strictly required to have the double quotes when it comes to the parser. Just make sure that the user variable, SomeDate that you created in SSIS has a DateTime data type and that it is scoped properly.|||

Please pardon my ignorance, I'm still not getting this.

I have the following variable:

Name Scope Data Type

SomeDate Package DateTime

The For Loop has the following:

InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)

Here is the error I receive:

Error at SomeDate Loop: The data types "DT_DATE" and "DT_WSTR" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Thanks again for your patience and assistance.

|||Select the For loop and ensure that you don't have another variable of the same name, SomeDate, scoped to the for loop. (You can have the same named variables but with different scopes.) I'm expecting to see a SomeDate listed somewhere with a data type of String.

for instance: How do I get the quantity of the last Date for every Location ?

Hello,

How do I get the quantity of the last Date for every Location ?
See example given:

select
[Measures].[Quantity] on columns,
(
nonemptycrossjoin(
[Location].[Location].[Location]
, [Posting Date].[Posting Date]
, 2
)
)
on rows
from
[Logistics]

This query will give me the following result:

Locations

Date

Quantity

Location A

2006-01-01

5

Location A

2006-01-05

3

Location A

2006-01-07

7

Location B

2006-01-01

2

Location B

2006-02-05

3

Location C

2006-01-01

2

Location C

2006-01-01

2

Location C

2006-01-02

3

Location C

2006-01-03

3

This is what I want

Location A

2006-01-07

7

Location B

2006-02-05

3

Location C

2006-01-03

3

Kind regards.

You should take a look at LastNonEmpty aggregation function in AS2005. It does exactly what you need.

|||Let me refine this with another example:

Date	Location	Horz. Group	Horz. Code	Vert. Group	Vert. Code	Item	Quantity
2006-1-1	A	6	1	5	1	I10256	1
2006-1-2	A	6	1	5	1	I10256	2
2006-1-3	A	6	1	5	1	I10256	3
2006-1-1	B	6	1	5	1	I10256	4
2006-1-3	B	6	1	5	1	I10256	5
2006-1-1	B	6	1	5	2	I10256	6

The actual supply-result for the year 2006 = 3 + 5 + 6 = 14 (this is correct)
The LastNonEmpty-result in AS2005 = 3 + 5 = 8 (this is not correct, eg. is not sorted by date as you can see but doesn't matter :o)
I actually wanna have the LastNonEmpty grouped by [Date],[Location],[Horz etc],[Vert etc],[Item] ?
How can I force the LastNonEmpty to do that ? Not only Time-related !?
I wanna see the correct supplies in my time-dimension on the level year or month or day :o)
Kind regards.

For a user of RS2000 but a non-programmer

Can someone tell me how to utilize the new calendar control in 2005. do i
have it right that it is supposed to give me a pop-up to choose a date from?
I have looked all over the msdn and the newsgroup here and havent found
anything real definitive. it could very well be that i dont know what im
looking for. i found learning the reporting service to be fairly intuitive
and well documented enough to do basic reporting with little programming.
so im wondering since i cant find out how to do this if it is because it
requires a code-behind. if it does could you point me to the docs on how.
i may be able to trudge through it with my limited programming knowledge.
TIA for any help.
MattWhat is most likely happening is that your parameter is defined as text, not
as a date/time. If date/time it automatically shows up. In layout mode,
Report menu, Report Parameters. Click on the parameter and change to
date/time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.matt.com> wrote in message
news:ugk9pK37FHA.3804@.TK2MSFTNGP12.phx.gbl...
> Can someone tell me how to utilize the new calendar control in 2005. do i
> have it right that it is supposed to give me a pop-up to choose a date
> from?
> I have looked all over the msdn and the newsgroup here and havent found
> anything real definitive. it could very well be that i dont know what im
> looking for. i found learning the reporting service to be fairly
> intuitive
> and well documented enough to do basic reporting with little programming.
> so im wondering since i cant find out how to do this if it is because it
> requires a code-behind. if it does could you point me to the docs on how.
> i may be able to trudge through it with my limited programming knowledge.
> TIA for any help.
> Matt
>|||that was way to easy....one last question...you dont get to get away that
easy...is there a time option...i want to add a default time...would i
have to add the time string to the end of the parm field once i get it
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23iQfT%2337FHA.1864@.TK2MSFTNGP12.phx.gbl...
> What is most likely happening is that your parameter is defined as text,
not
> as a date/time. If date/time it automatically shows up. In layout mode,
> Report menu, Report Parameters. Click on the parameter and change to
> date/time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.matt.com> wrote in message
> news:ugk9pK37FHA.3804@.TK2MSFTNGP12.phx.gbl...
> > Can someone tell me how to utilize the new calendar control in 2005. do
i
> > have it right that it is supposed to give me a pop-up to choose a date
> > from?
> >
> > I have looked all over the msdn and the newsgroup here and havent found
> > anything real definitive. it could very well be that i dont know what
im
> > looking for. i found learning the reporting service to be fairly
> > intuitive
> > and well documented enough to do basic reporting with little
programming.
> > so im wondering since i cant find out how to do this if it is because it
> > requires a code-behind. if it does could you point me to the docs on
how.
> > i may be able to trudge through it with my limited programming
knowledge.
> >
> > TIA for any help.
> >
> > Matt
> >
> >
>