database collation: date

Showing posts with label date. Show all posts

Tuesday, March 27, 2012

Changing the name of the Table dynamically inside the SP.

hi All,

I get a daily dump of some data in a table like DumpTable_07182007, where the suffix is the date of the data dumped. Everyday a new table is created with the date as suffix as according to the date like DumpTable_07192007, DumpTable_07202007 etc.We get day before yesterday data on today.i.e. on 20 July we get the data for 18 july in the table DumpTable_07182007 and so on. Similarly on 21 July we will get the data in the table DumpTable_07192007 . I have to create a SP (to include in a job) that pulls some data from these dump tables in such a way that if the SP is fired by a job on 20July , it shud pull the data from DumpTable_07182007 . Similarly if the SP is fired on 21 july , it shud pull the data from DumpTable_07192007 table only . etc.

So my problem is how to change the table name dynamically inside the SP so that whenever the SP is fired on a particular date , it shud pull data from the appropriate table.

I hv something like : select count(*) from DumpTable_07182007 where abcd =1 ,

query inside my SP.

Plz guide me in dynamically changing the tablename in the SP .

Thanks in advance.

Hi,

You can not have a dynamic table name in a query. One way to do this is to generate your query as a text each time, and then execute it using sp_executesql.

If you want to use its output as a table, then you can also create a table-value function, and pass the date to it. It should create a table in script and execute it and return that result.

Zafar|||

Hi,

You have to frame the select query as a string (VARCHAR), and then execute the statement.

By using this logic, you can dynamically modify not only your table name but the entire query.

example: ( similar to this )

DECLARE @.myQuery VARCHAR(200)

SET @.myQuery = 'SELECT COUNT(*) FROM DumTable_' + @.DateParameter

EXECUTE (@.myQuery)

Regards,

Perumal.R,

Prelude Solution Providers India Pvt. Ltd.,

Kotturpuram,

Chennai.

|||

You don't need to use dynamic SQL. You can try the approach below which is more easy to debug and maintain. This requires the caller to have create view permission and I assume this is probably ok in this case since this looks like a batch job on the server. So you can run it under dbo account.

1. Create a SP that create a view dynamically. The view will refer to the DumpTable being loaded. For example:

Code Snippet

create procedure CreateDumpTableView (@.date varchar(10))

begin

declare @.tablename nvarchar(130);

set @.tablename = quotename(N'DumpTable_' + @.date);

exec('create view DumpTableRef as select .... from ' + @.tablename);

end

2. Now, in your main SP write your queries against the view and call the the create view SP first when the date changes like:

Code Snippet

create procedure YourSp (@.date varchar(10))

begin

exec CreateDumpTableView @.date;

if @.@.error....

select count(*) from DumpTableRef;

end

sql

Sunday, March 25, 2012

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
Simon
Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>
|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The application
s
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main probl
ems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonSimon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is
> that dates are being stored in the British format and I desperately need t
hem to be stored in the
> American format. The applications I'm making are all for the American mark
et and having the dates
> stored in the db as british dates is causing all sorts of problems. The tw
o main problems is that
> my application sends american formatted dates to the application (which th
e db freaks out at) and
> the database tries to send back british dates (which my application freaks
out at).
> Can anyone tell me, is there a way to have the DB store the dates in the A
merican format? I was
> hoping that this could be set on a per database basis because I do have a
couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simonsql

Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
SimonHi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>|||Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.com...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon

Thursday, March 22, 2012

Changing the column color depending on a dynamic date

Hi, I have a series of columns which I need to be able to change the
background color of dynamically depending on a date which is computed
dynamically when the report is created.
I a using a textbox to display the dynamically calculated date
(e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
But I cannot seem to reference this textbox object at runtime.
I also need to know how to get the actual dayname given the calculated
date above.
Any ideas about how I can do this'
Thanks
MarkusOn Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> Hi, I have a series of columns which I need to be able to change the
> background color of dynamically depending on a date which is computed
> dynamically when the report is created.
> I a using a textbox to display the dynamically calculated date
> (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> But I cannot seem to reference this textbox object at runtime.
> I also need to know how to get the actual dayname given the calculated
> date above.
> Any ideas about how I can do this'
> Thanks
> Markus
You should use something like this in a new dataset that populates a
hidden report parameter:
SELECT DATENAME(DW, GETDATE())
Then reference the hidden parameter as part of the table's column
background property. Something like this should work:
=iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
>
>
> > Hi, I have a series of columns which I need to be able to change the
> > background color of dynamically depending on a date which is computed
> > dynamically when the report is created.
> > I a using a textbox to display the dynamically calculated date
> > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > But I cannot seem to reference this textbox object at runtime.
> > I also need to know how to get the actual dayname given the calculated
> > date above.
> > Any ideas about how I can do this'
> > Thanks
> > Markus
> You should use something like this in a new dataset that populates a
> hidden report parameter:
> SELECT DATENAME(DW, GETDATE())
> Then reference the hidden parameter as part of the table's column
> background property. Something like this should work:
> =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
Thanks Enrique, I will give that a shot
Cheers
Markus|||On Apr 12, 5:00 pm, Markus...@.gmail.com wrote:
> On Apr 12, 1:07 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Apr 11, 7:19 pm, Markus...@.gmail.com wrote:
> > > Hi, I have a series of columns which I need to be able to change the
> > > background color of dynamically depending on a date which is computed
> > > dynamically when the report is created.
> > > I a using a textbox to display the dynamically calculated date
> > > (e.g. =DateAdd(DateInterval.Day,5,Today).ToString("dd-MMM") )
> > > But I cannot seem to reference this textbox object at runtime.
> > > I also need to know how to get the actual dayname given the calculated
> > > date above.
> > > Any ideas about how I can do this'
> > > Thanks
> > > Markus
> > You should use something like this in a new dataset that populates a
> > hidden report parameter:
> > SELECT DATENAME(DW, GETDATE())
> > Then reference the hidden parameter as part of the table's column
> > background property. Something like this should work:
> > =iif(Parameters!HiddenParameterName.Value = "Monday", "Red", "White")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> Thanks Enrique, I will give that a shot
> Cheers
> Markus
You're welcome. Let me know if you need further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Saturday, February 25, 2012

Changing number to date

I have a table in SQL Server that has a date field whose properties are 'number'. I want to export this data to a table in Oracle. I can't touch the Server table to alter it to be a date. Is there some way I can do this in the Oracle table? Example- SQL Server= 20030324 (March 24,2003). In Oracle I want it to read '24/03/2003', or some other date format.declare @.d as int
set @.d = 20031105
select cast(substring(cast(@.d as varchar(8)),5,2)+ '/' +
substring(cast(@.d as varchar(8)),7,2)+ '/' +
left(cast(@.d as varchar(8)),4)as smalldatetime)|||I need to update a whole column in an Oracle table. Thanks.

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Hi guys,

just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?

I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.

I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?

Thanks!

sluggy

This report sample may help.

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>aad8c909-02c6-4fb6-841c-0557ea327ec4</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Product].[Product Categories].[Category].&[1]</Value>

</Values>

</DefaultValue>

<Prompt>Product Categories</Prompt>

<DataSetName>ProductProductCategories</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<DataType>DateTime</DataType>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox16</rd:DefaultName>

<Style>

</Style>

<Value>Category Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox17</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox18</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Category.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox13</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox14</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox15</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

<rd:DefaultName>textbox26</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox27</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox28</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Aggregate(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox21</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Subcategory.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox23</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox24</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox25</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox22</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Product.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!InternetSalesAmount.FormattedValue</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!ResellerSalesAmount.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

<Value>Product</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Internet Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Reseller Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>03e1fd95-077c-4176-a8c7-4d09893efe4e</rd:ReportID>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<Value>=Parameters!ProductProductCategories.Value</Value>

</QueryParameter>

<Value>="[Date].[Date].[" & CDate(Parameters!DateDate.Value).ToString("MMMM d, yyyy") & "]"</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Internet Sales Amount</MeasureName><UniqueName>[Measures].[Internet Sales Amount]</UniqueName></ID><ItemCaption>Internet Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Total Sales</MeasureName><UniqueName>[Measures].[Total Sales]</UniqueName></ID><ItemCaption>Total Sales</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions><ParameterDefinition><Name>ProductProductCategories</Name><DefaultValues><DefaultValue>[Product].[Product Categories].[Category].&[1]</DefaultValue></DefaultValues><Caption>Product Categories</Caption><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Product Categories].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition><ParameterDefinition><Name>DateDate</Name><DefaultValues><DefaultValue>[Date].[Date].[All Periods]</DefaultValue></DefaultValues><Caption>Date.Date</Caption><HierarchyUniqueName>[Date].[Date]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

</DataSet>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

|||

Thanks Teo, I'll pull that apart and have a look at it.

Cheers

sluggy

|||

Note that whatever you do with the date parameter, you need to comply with the AS member format, e.g. [Date][Day].[Day].&[20070213]. For the sample report, I think I pulled a little trick where I changed the Value property of the dimension key in the AW Date dimension to load the DateTime value. Also you can try:

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

Changing Formats

Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm

The file is being pulled into a varchar field.... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006

any suggestionsStop loading it into a varchar field. Load it into a datetime column so you can display it however you want.

Thursday, February 16, 2012

changing English Date to Iranian date

Hi Everyone,

I want to change the form of Date in SQL Server 2005,from English Date,to Iranian Date.

For example, instead of inserting 2007/1/1 write 85/10/11.

Thanks,

Nassa

I am not familiar with the iranian date, is the first date you mentioend correlated to the second one ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

FROM SQL Server 2005 Books Online topic 'CAST and CONVERT (Transact-SQL)'.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.

See for custom code:
http://www.codeproject.com/useritems/Hijri_Shamsi_Date.asp?df=100&forumid=254936&exp=0&select=1371550

General international resource:
http://www.microsoft.com/globaldev/DrIntl/columns/002/default.mspx

Sunday, February 12, 2012

changing datetime to string?

As in the database, i made a few columns in the forum table.
date(datetime) 15/09/2004 3.35PM
author(char) John

Select datetime + '<br>' + author from forum

it claimed there is an error on this datetime.

By right, the result should be

15/09/2004 3.35PM
John

can anyone help me how i could get the result out without having to change date's properties in the sql database?

Will be greatly appreciated if help gets ard.It looks like that select statement is trying to perform a math function. You should select the fields individually, then format them appropriately in your vb/cs code.

select datetime, author from forum

In your code, you will now have two fields exposed, and you can concatenate them if you wish.|||The TSQL CONVERT function can do that for you or Google for using string.format and use a date format code to convert it to the type of string you want it to be from within your VB code.

Changing date to string of numbers

I basic question but can someone help.

I have a SELECT statement, the result of which populates adatagrid. The first column has consecutive dates in it and I wantto hyperlink each date to a seperate Javascript function (theJavascript is created on the fly and is unique for each date). Ineed a different function name for each function and so tried the datebut "/" is not allowed in the Javasript function name. I thinkthe easiest way will be to produce a new column with the date expressesddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-

"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]

but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.

I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!

Many thanks

Mike

Hi Mike,

You can use the ISO format in this place:

CONVERT(NCHAR(8), [Date], 112)AS newDate

the date format will be yyyymmdd.

You can always check CONVERT DATE function from Books Online to convert your date.

Hope this helps.

|||Hi Limno

Many thanks. Your reply is just what I need. It works great.

I am sorry that could not figure it out for myself. I do usebooks on line and I have "Microsoft SQL Server 2005 A Beginner'sGuide" (which I got before I realise my server uses 2000!) and "SAMSTeach Yourself SQL 24 Hours". I started off learning VB.net butas my project goes on, rather than feeling that I am becoming competentat producing the web pages I want, I seem require more and moreknowledge (like SQL, JavaScript & CSS). Sometimes I feel I amgetting there, the next minute feel totally inadequate!! I strive tolearn, and in the meantime I really do appreciate the help of peoplelike yourself.

Many many thanks for your time and patience.

Regards

Mike

changing date formats

Hello. I am using Microsoft SQL Server Management Studio (SQL Server 2005). When I select a date column from a table, the date is displayed in "mm/dd/yyyy hh:mm:ss" format. Is there a way i can change this date format so that it shows "dd/mm/yyyy hh:mm:ss" permanently? Thanks.If u're using VB then it can be done.
say its a DateTimePicker so...
Format(dtp.Value, "d/M/yyyy")
|||If you want to do it in SQL Server check out the CONVERT function.
It takes three parameters (for dates)
eg. SELECT CONVERT(varchar, GetDate(), 121)
Check out BOL for possible values and a description of the last parameter, this defines the format.
hth

Changing date and keeping time

Hi! I'm using MS SQL server 2000. How can I change date in date field keepin
g
time the same (datetime field)? I'm using SQL Enterprise Manager for that.
Please help with syntax. More thanks, Alar. PS! Can You suggest some book or
other source I can find hints about MS SQL syntax?
> Hi! I'm using MS SQL server 2000. How can I change date in date field
> keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
That wont work, if you edit the date via EM you always will (implicit)
issue a command like:
UPDATE Sometable SET Somecolumn '01/01/2004 00:00:00' Where ...
even if you type in '01/01/2004'. The function DATEADD or if you prior cut
out the time and put it on the changed column (time 00:00:00) would work
fine.
PS! Can You suggest some book or
> other source I can find hints about MS SQL syntax?
Did you try BOL ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||> Hi! I'm using MS SQL server 2000. How can I change date in date field
keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax.
See function CONVERT in BOL.
Example:
update table1
set c1 = '2005-05-09' + right(convert(varchar(25), c1, 126), 13)
where c1 >= '20050501' and c1 < '20050508'
AMB

> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?
MS SQL Server 2000's Books Online
AMB
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||The DateAdd() function can add arbitrary number of days, w

s, months, or
whatever, to a given date. If you choose any time increment greater than a
day, the time portion of the value will remain the same...
Otherwise, you need to update the column to a new date with the same time as
the the datetime that's in there...
Update TablleName Set
DTColumn = 'NewDate as CCYYMMDD ' +
convert(VarCHar(12), DTColumn, 14)
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||I don't recommend using Enterprise Manager for this, and
I suggest you run the update query in Query Analyzer
instead.
If @.newDate is the new date, T is your table, myDate is the
column you want to change, and rowKey = @.rowKey identifies
the row you want to change, this will work (not tested - watch
for typos)
update T set
myDate = dateadd(day, datediff(day, myDate, @.newDate), myDate)
where rowKey = @.rowKey
This will add a whole number of days to myDate, the number
it adds being exactly the number of whole days from myDate
to @.newDate.
Steve Kass
Drew University
Alar Pandis wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?

Changing Date

Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).

In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.

The package and report run fine. One of the columns in the table is for a date (date of birth). The SSIS package gathers the column information and inserts it into SQL Server as a Decimal datatype (Decimal 6,0). The dates (decimals?) are now in the format 40207, as they were on the 400, where that specific date would be April 2, 2007. 120707 would be December 7, 2007 and so on. I would like to format the date to be mm/dd/yy or even m/dd/yy for the "single digit" months in the report for easy readability.

I have tried setting the format options in reporting services for formatting the field as a date, and even using the cdate conversion or FormatDateTime in an expression. I cannot seem to get the date to change. I either get an "# error" for the values of the fields or I get the same date that already was there (40207)?

It seems as if none of the date formatting options are working. Is this something I need to do on the package (set the mappings on the create table to create the columns as a Date, not Numeric or something similar?), or should it be easier than that by converting the output of the report to display mm/dd/yy with the "/"?

Any help is greatly appreciated.

Thanks

Expression-> Common Functions -> Date & Time -> Month || Day || Year|||

Although I am confused as to why you are storing a date value in a decimal field, this can be shown correctly via the report. Try this from your select query:

left(convert(varchar, <datefield>), len(convert(varchar, <datefield>)) - 4) + '/'
+ left(right(convert(varchar, <datefield>), 4), 2) + '/'
+ right(convert(varchar, <datefield>), 2)

Or...

reverse(stuff(stuff(reverse(<datefield>), 3, 0, '/'), 6, 0, '/'))

However, I would recommend that you update the SSIS package to handle the dates and put them in a datetime field.

Hope this helps.

Jarret

|||

Thanks for the replies.

Kenny - I don't have that option, although they are there individually. Are you meaning that I type that in along with something like (=Fields!DOB(etc))

If so, I may be doing it incorrectly, but I cannot get any of the "common" functions to work with my date/time stuff. I did however have good luck integrating the "globals" on my page (execution time, etc).

Jarrett, thanks for the reply, I haven't got to trying that part yet.

Thanks again everyone.

|||

The Month, Day, & Year functions would work if your field was already a date, but it's not, it's a decimal.

The functions that Kenny mentioned require that you pass in a date type. If you try to use them by passing a decimal type, you will get the following error:

Conversion from type 'Decimal' to type 'Date' is not valid.

Jarret

|||

Thanks. How should I put it in the SSIS package to convert? I tried using a data transformation, but got an error. The data is stored in the DB2 database as a decimal, I was just bringing it over with a simple SSIS package.

Thanks for your help.

|||

Within SSIS, in your source connection's SQL command, add a column with the statement:

reverse(stuff(stuff(reverse(convert(varchar(6), <datefield>)), 3, 0, '/'), 6, 0, '/')) as DecimalDate

Then, in your SQL database destination, map DecimalDate (instead of <datefield>) to your date type field in your SQL table.

Hope this helps.

Jarret

|||Thanks.|||

Did this fix your issue?

Jarret

|||

Yes, I can use the reverse method you described above and produce the results I want. However, I have not been successful in making the dates come over in the SSIS package. I must say though, I have been busy and haven't been completly dedicated to that recently.

Thanks for your help.

Changing Date

Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).

In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.

Any help is greatly appreciated.

Thanks

Expression-> Common Functions -> Date & Time -> Month || Day || Year|||

Although I am confused as to why you are storing a date value in a decimal field, this can be shown correctly via the report. Try this from your select query:

left(convert(varchar, <datefield>), len(convert(varchar, <datefield>)) - 4) + '/'
+ left(right(convert(varchar, <datefield>), 4), 2) + '/'
+ right(convert(varchar, <datefield>), 2)

Or...

reverse(stuff(stuff(reverse(<datefield>), 3, 0, '/'), 6, 0, '/'))

However, I would recommend that you update the SSIS package to handle the dates and put them in a datetime field.

Hope this helps.

Jarret

|||

Thanks for the replies.

Kenny - I don't have that option, although they are there individually. Are you meaning that I type that in along with something like (=Fields!DOB(etc))

Jarrett, thanks for the reply, I haven't got to trying that part yet.

Thanks again everyone.

|||

The Month, Day, & Year functions would work if your field was already a date, but it's not, it's a decimal.

The functions that Kenny mentioned require that you pass in a date type. If you try to use them by passing a decimal type, you will get the following error:

Conversion from type 'Decimal' to type 'Date' is not valid.

Jarret

|||

Thanks for your help.

|||

Within SSIS, in your source connection's SQL command, add a column with the statement:

reverse(stuff(stuff(reverse(convert(varchar(6), <datefield>)), 3, 0, '/'), 6, 0, '/')) as DecimalDate

Then, in your SQL database destination, map DecimalDate (instead of <datefield>) to your date type field in your SQL table.

Hope this helps.

Jarret

|||Thanks.|||

Did this fix your issue?

Jarret

|||

Thanks for your help.

Changing datatype from char to datetime

I am trying to run the following query:

ALTER TABLE dnb_profile
ALTER COLUMN [family update date] datetime

and I keep getting the following error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

Can anyone tell me how I can do this successfully??

Thanks,

Connie Sawyer
Foley & Lardner
clsawyer@.foley.comOn 27 Sep 2004 09:15:29 -0700, Connie Sawyer wrote:

>I am trying to run the following query:
>ALTER TABLE dnb_profile
>ALTER COLUMN [family update date] datetime
>and I keep getting the following error:
>Server: Msg 242, Level 16, State 3, Line 1
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value.
>The statement has been terminated.

Hi Connie,

This indicates that at least one value currently in the [family update
date] column is of a format that won't convert to SQL Server properly.
There may be various explanations:

1. Someone managed to enter some gibbledygook in the column - possible,
since it's of the char data type. True rubbish would result in another
error message, but dates like february 30, december 53 or some date in
month number 17 would yield this message.

2. The contents of the column may look like normal dates to you, but not
to SQL Server. The error message you got is quite common if SQL Server
interprets day as month and month as day. Remember that there are manu
different notation styles for dates. The only unambiguous date formats are
yyyymmdd (for date only) or yyyy-mm-ddThh:mm:ss.mmm (for date and time,
where .mmm, denoting the milliseconds, is optional).

In each case, you'll have to inspect your data to find the cause and
either manually fix the offending rows (if there are just a few) or do
some string massaging to change from a misunderstood date format to one of
the standard formats before converting.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Connie Sawyer wrote:

> I am trying to run the following query:
> ALTER TABLE dnb_profile
> ALTER COLUMN [family update date] datetime
> and I keep getting the following error:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> The statement has been terminated.
> Can anyone tell me how I can do this successfully??
> Thanks,
> Connie Sawyer
> Foley & Lardner
> clsawyer@.foley.com

What you should do is to run this:

SELECT * FROM dnb_profile
WHERE ISDATE([family update date])=0

This will return you all the records
where value of [family update date] can't be converted to date.
And you should fix those records before altering the column.

Here's the link to the isdate function:

http://msdn.microsoft.com/library/d..._ia-iz_8ov9.asp

WYGL,
Andrey

Changing Database Server Locale Date time settings ?

Hi There

We currently have the following scenario:

4 app servers with regional date and time settings of locale A.

1 database server with locale settings B.

What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.

It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?

In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?

Thanx

As long as date/time values are stored in datetime datatypes, changing the locale/regional settings 'should' not have any impact on the data.

If ServerA uses the form of 'dd/mm/yyyy' and attempts to pass that string value to serverB (and ServerB uses the form of 'mm/dd/yyyy', there is confusion and often failure. Is '06/12/2007' June 12th, or Dec 6th?

However, if you were to make sure that any time values passed to SQL procedures and functions was in the form of 'yyyymmdd' or 'yyyy/mm/dd' (standard ISO format), there would not be a problem for one server to interpret the date from a different server.

Tuesday, March 27, 2012

Changing the name of the Table dynamically inside the SP.

Sunday, March 25, 2012

Changing the default date format for a database

Changing the default date format for a database

Changing the default date format for a database

Thursday, March 22, 2012

Changing the column color depending on a dynamic date

Saturday, February 25, 2012

Changing number to date

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Changing Formats

Thursday, February 16, 2012

changing English Date to Iranian date

Sunday, February 12, 2012

changing datetime to string?

Changing date to string of numbers

changing date formats

Changing date and keeping time

Changing Date

Changing Date

Changing datatype from char to datetime

Changing Database Server Locale Date time settings ?

database collation

Blog Archive

About Me