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 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 Time......... using DateTime field

hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do.....

select DATEPART(hour, loginTime) .....returns me the hour i can get the Hour part of the time but the prblem is how to identify it

whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level

any ideas????

i used following query..

my table is like Finddate

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000
4 2007-01-01 23:30:00.000
5 2007-01-01 22:30:00.000

and i am selecting the records between 1 to 2 pm.

1 pm means 13 and 2 pm means 14, so i written query

select*from finddatewheredatepart(hour,bdate)>=13anddatepart(hour,bdate)<=14

this will gives me result as

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

hope this will help u..

|||

hi Mahadeomatre thnx for ur help i got the solution,ur query should b like this.

select*from finddatewheredatepart(hour,bdate) >=13anddatepart(hour,bdate)<14

then according to ur table it wil return only 1st row

1 2007-01-01 13:30:00.000

and "datepart(hour,bdate)<14" will omit the other two rows as per requirment.

2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

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 just the Date portion of DateTime fields

What's the best way to compare just the Date portions of datetime
fields (ignore the time)
lateFlag = case
when TargetEndDate is null then 'N'
when TargetEndDate < getutcdate() then 'Y'
else 'N'
end
RonUse the CONVERT function to get the desired component of a date time.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
> What's the best way to compare just the Date portions of datetime
> fields (ignore the time)
> lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
> Ron
>|||If you want a condition that tells if TargetEndDate has a date
part before the date part of getutcdate(), you can do it while
still taking advantage of any useful index on TargetEndDate
by checking whether TargetEndDate with its time part is
before the date-only part of getutcdate() this way:
TargetEndDate < dateadd(day,datediff(day,0,getutcdate())
,0)
The time part of TargetEndDate can't change whether it
is before a date-only value or not.
Steve Kass
Drew University
RonL wrote:

>What's the best way to compare just the Date portions of datetime
>fields (ignore the time)
>lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
>Ron
>
>|||Or better yet a couple datetime functions:
select dateadd(d, datediff(d, 0, current_timestamp), 0)
so the OP's snippet of code would be something like
lateFlag =
case
when TargetEndDate is null then 'N'
when dateadd(d,datediff(d,0,TargetEndDate),0)
<
dateadd(d,datediff(d,0,getutcdate()),0) then 'Y'
else 'N'
end
*mike hodgson*
http://sqlnerd.blogspot.com
SriSamp wrote:

>Use the CONVERT function to get the desired component of a date time.
>--
>HTH,
>SriSamp
>Email: srisamp@.gmail.com
>Blog: http://blogs.sqlxml.org/srinivassampath
>URL: http://www32.brinkster.com/srisamp
>"RonL" <sal_paradise_93@.yahoo.com> wrote in message
>news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>
>
>|||Don't use CONVERT for this. Use the DateAdd and DateDiff version, (it's
already been posted twice, so I won't repeat)
The reason for this is that Convert writes the result to a memory page.
This means SLOW AND EXPENSIVE! Using the DateAdd and DateDiff functions
does not result in a page write, it only uses a small amount of CPU whilst
running the functions, and there is no conversion of datatypes taking place.
Regards
Colin Dawson
www.cjdawson.com
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:etjXwS1ZGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Use the CONVERT function to get the desired component of a date time.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "RonL" <sal_paradise_93@.yahoo.com> wrote in message
> news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>|||Great. Thanks.
Ron

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.