How to control format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?

How to configure distributor thread for being up while replicated database is down

Hi
I often deal with a problem when replicated database is down due to electric outage or any other scheduled type of downtime. Primary database is up but distribution agent is going down 'after 10 attempts to connect to replicated database'. That's the problem I think because not only distribution agent goes in undetermined state and I have to restart sqlagent service, but I need to go through whole process of snapshot and applaying it to replicated db. I would like it to accumulate transactions in distribution database and once replicated db is up - distribute them.
I wonder if anybody can tell me how to configure distribution thread so it does not go down after 10 unsuccessfull attempts but stays for day or two.
Thank you, Gene.

Gene, why do you need to re-apply the snapshot, is your retention period very small? By default it's 72 hours, so if distribution agent can get back up within that time period, it should just pick up where it left off.

Unfortunately if your distribution agent is running in Continuous mode (which you mention since it goes down after 10 unsuccessful attempts), the workaround would be change it so that the agent runs on a scheduled interval, like every minute. This way in the case the machine gets knocked off the network, the agent will always run every N minutes, pass or fail. As soon as the machine gets back on the network, it should pick up where it left off, provide the oldest txn that hasn't been replicated is within the retention period.

|||

The other possible solution is to increate the number of retry attempts for your distribution agent job, when you view job steps of an agent job, there is an advanced tab, on that screen, you can specify a big value for "Retry Attempts" and how often you want the retry to happen "Retry Interval (minutes)". For example, if you want the retry lasts for 2 days, you could put 2880 for Retry Attempts and 1 for Retry Interval.

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I don't think this exists for SQL 2000, but if it does, then this is also a good solution.

How to compare vachar which type :20060324225008 with Datetime?

in my SQL 2000

the column importDate contain Date as a vachar , type is 20060324225008 ( 2006 -year , 03-month, 24-day)

I want to compare this column with today's date, how to transform it?

how to return value 20060324 not 20060324225008?

thank you

Grab the column as a string

string rawNumber = "20060324";

DateTime dtTime = Convert.ToDateTime(rawNumber);

Does it work?

|||

how can I get the value of column = 20060324, is was20060324225008 not 20060324

the problem is how to get 20060324 only

thank you

|||

Select LEFT(yourColumn,8) as newValue FROM yourTable

After this, you can use one of datetime functions to compare this date part with today's date part.

How to compare the date in SQL Server 2000?

Hi all,
I have encountered a problem that I can't compare the date in SQL Server 2000.
But I know that maybe I have used "datetime" as data type for that field.
My question is that, how can I get ONLY the "date" in stead of "datetime" from the database?
Your attention is appreciated!
Thank you.

Regards,
katszetoYou can use Convert to get the date as a string, but SQL itself doesn't have a date only type. You need to use a temp var or datediff functions if you want to ignore the time.|||Is it first get the value from the database and then convert it to String and do comparison?|||you should always strive to avoid string comparisons for dates, what/where would be the ideal code you are looking for and we'll see what sort of solution you should have?|||Exactly what are you trying to do?
Give some requirements and then perhaps we can
give some more relevant help.|||One option:
Convert the date to a varChar specifying a format that does not involve time. Then CAST() the varchar back to datetime

WHERE CAST(CONVERT(varChar, myField, 112) AS SmallDateTime) = '1900/1/1'

Cheers
Ken|||Now, I want to enter a date as a parameter and search from the database.
Then display the result in the datagrid.
And the SQL Command is :Select * from [table] where date = [parameter]
I have tried that if I also input the date and time as parameter,
no record is found!!|||

CREATE PROC usp_myProc
 @.Param smallDateTime
AS
 -- Do some SELECT here
 WHERE
 CAST(CONVERT(varChar, myField, 112) AS SmallDateTime) = @.Param

Cheers
Ken|||You'd be much better off using DateDiff for that sort of operation.

how to compare data of text type?

We can't use the term "=" to compare the data directly ,of which type
is text or ntext. Are there other good ideas to get the exact matchs?
In T-SQL you can use the LIKE operator without any wildcard characters.
However, you'd much better off using an appropriate text comparison tool on
the client.
ML
http://milambda.blogspot.com/
|||Do hierarchy of tests, first check datalength to see if they are the same,
if not bail stating difference, if they are, then you need to do byte by
byte comparisons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"chemInformatic" <cdfuwu@.hotmail.com> wrote in message
news:1133871808.033165.299100@.g47g2000cwa.googlegr oups.com...
> We can't use the term "=" to compare the data directly ,of which type
> is text or ntext. Are there other good ideas to get the exact matchs?
>

how to compare current date with sql db datetime data type

Hi,

I am using one datetime data type ( name: date_added ) and getdate() as default value. I want to display only those records added today. How I can compare current date with date_added.

Thanks
ManojSELECT * FROM tablename WHERE CONVERT(varchar(10),date_added,112)=CONVERT(varchar(10),GetDate(),112)

This converts both dates to yyyymmdd format, and then compares them. A better alternative might be:

SELECT * FROM tablename WHERE date_added>=CONVERT(datetime,CONVERT(varchar(10),GetDate(),112))

This presumes that there are no records added later than today. It compares the date_added to getdate() return value, converted to yyyymmdd, then converted back to a date.

How to Compare 2007-9-11 and Month(GetDate()) ?

I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)

I write code

select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)

it will like

select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)

→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30

but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code

pleae help... thank you very much

Please do not open multiple threads for same question. Refer:http://forums.asp.net/t/1176161.aspx.