how to exclude time portion of datefield in a textbox?

Hello,
The datasourece in my report contains a date field, and the time portion of
this date field is showing up. Is there a format function that I can use in
the textbox or do I need to remove the time portion at the datasource level?
If there is some kind of formatting I can do in the textbox - what does that
look like? What is the syntax?
Thanks,
RichIn the textbox properties, format tab, format code, use 'd'. The format
codes are from .Net you can also use the format function if you need to
concatenate. The elipsi button shows more options.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> Hello,
> The datasourece in my report contains a date field, and the time portion
> of
> this date field is showing up. Is there a format function that I can use
> in
> the textbox or do I need to remove the time portion at the datasource
> level?
> If there is some kind of formatting I can do in the textbox - what does
> that
> look like? What is the syntax?
> Thanks,
> Rich|||Thanks. I will give that a try. Although, I did tweak the datasource a
little and added this which also does the trick (just not at the report level
is the thing):
convert(char(8), s.CurExpireDate, 1) CurExpireDate
which returns say - 10/18/06 format
where CurExpireDate was the datefield in my datasource.
May I ask what the syntax looks like using 'd' in the report textbox?
= ...Fields!CurExpireDate.Value
"Steve MunLeeuw" wrote:
> In the textbox properties, format tab, format code, use 'd'. The format
> codes are from .Net you can also use the format function if you need to
> concatenate. The elipsi button shows more options.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> > Hello,
> >
> > The datasourece in my report contains a date field, and the time portion
> > of
> > this date field is showing up. Is there a format function that I can use
> > in
> > the textbox or do I need to remove the time portion at the datasource
> > level?
> >
> > If there is some kind of formatting I can do in the textbox - what does
> > that
> > look like? What is the syntax?
> >
> > Thanks,
> > Rich
>
>|||The expression in the textbox would the the same:
=Fields!CurExpireDate.Value
the textbox is applying the formatting to the contents of the textbox.
If you wanted to concatenate strings then the expression would look
something like this:
= "Generated on " & Format(Globals!ExecutionTime, "d")
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> Thanks. I will give that a try. Although, I did tweak the datasource a
> little and added this which also does the trick (just not at the report
> level
> is the thing):
> convert(char(8), s.CurExpireDate, 1) CurExpireDate
> which returns say - 10/18/06 format
> where CurExpireDate was the datefield in my datasource.
> May I ask what the syntax looks like using 'd' in the report textbox?
> = ...Fields!CurExpireDate.Value
>
>
> "Steve MunLeeuw" wrote:
>> In the textbox properties, format tab, format code, use 'd'. The format
>> codes are from .Net you can also use the format function if you need to
>> concatenate. The elipsi button shows more options.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
>> > Hello,
>> >
>> > The datasourece in my report contains a date field, and the time
>> > portion
>> > of
>> > this date field is showing up. Is there a format function that I can
>> > use
>> > in
>> > the textbox or do I need to remove the time portion at the datasource
>> > level?
>> >
>> > If there is some kind of formatting I can do in the textbox - what does
>> > that
>> > look like? What is the syntax?
>> >
>> > Thanks,
>> > Rich
>>|||Thank you for this information.
Rich
"Steve MunLeeuw" wrote:
> The expression in the textbox would the the same:
> =Fields!CurExpireDate.Value
> the textbox is applying the formatting to the contents of the textbox.
> If you wanted to concatenate strings then the expression would look
> something like this:
> = "Generated on " & Format(Globals!ExecutionTime, "d")
> Steve MunLeeuw
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:FCE38529-426A-41FC-88DD-EAF9974AB6ED@.microsoft.com...
> > Thanks. I will give that a try. Although, I did tweak the datasource a
> > little and added this which also does the trick (just not at the report
> > level
> > is the thing):
> >
> > convert(char(8), s.CurExpireDate, 1) CurExpireDate
> >
> > which returns say - 10/18/06 format
> >
> > where CurExpireDate was the datefield in my datasource.
> >
> > May I ask what the syntax looks like using 'd' in the report textbox?
> >
> > = ...Fields!CurExpireDate.Value
> >
> >
> >
> >
> > "Steve MunLeeuw" wrote:
> >
> >> In the textbox properties, format tab, format code, use 'd'. The format
> >> codes are from .Net you can also use the format function if you need to
> >> concatenate. The elipsi button shows more options.
> >>
> >> Steve MunLeeuw
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:93FE2808-E8D0-4F74-BF11-B669FE2718D6@.microsoft.com...
> >> > Hello,
> >> >
> >> > The datasourece in my report contains a date field, and the time
> >> > portion
> >> > of
> >> > this date field is showing up. Is there a format function that I can
> >> > use
> >> > in
> >> > the textbox or do I need to remove the time portion at the datasource
> >> > level?
> >> >
> >> > If there is some kind of formatting I can do in the textbox - what does
> >> > that
> >> > look like? What is the syntax?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>

How to enter a date parameter in Debugger

(This is prob. a really dumb question but it's driving me mad!!...)

I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.

Thanks.Got it ... finally!

yyyy-mm-dd

Argggggh.

How to encryt a Date field?

How do we encrypt/decrypt a datetime field? For e.g. Date of Birth

The way to encrypt any data type that cannot be implicitly converted to varbinary (such as datetime, integers, etc.) is to explicitly convert or cast the value to varbinary.

For example:

CREATE SYMMETRIC KEY key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

DECLARE @.mySecretDate datetime

DECLARE @.mySecretDateBlob varbinary(100)

SET @.mySecretDate = '01/02/2008 12:45PM'

-- Explicitly convert/cast the datetime variable to a varbinary

SET @.mySecretDateBlob = EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.mySecretDate))

SELECT @.mySecretDateBlob as 'datetime ciphertext'

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

go

I hope this information is useful.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul garcia,

How should the date field be stored in the database, should it be datetime or varbinary?

I am inserting data using a stored procedure that encrypts the date using encryption keys. I am using a view to decrypt the date.

Table design

Firstname -- varbinary(50)

secretdatevarbinary(100)

Stored procedure:

DECLARE @.FIRSTNAME varchar(100)

DECLARE @.secretdate datetime

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

insert into tbltest firstname, lastname, secretdate values(

EncryptByKey( key_guid('key_01'), @.FIRSTNAME),

EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.secretdate ))

close symmetic keys

How do i decrypt the date field?

Please help.Thanks in advance

Ks

|||

Hi Ks,

I'm not sure if I understand your question.

Encrypted data will be varbinary so the encrypted text should be stored in a varbinary column. As for decrypting the data, I am copying and pasting Raul's response from before:

DECLARE @.mySecretDateBlob varbinary(100)

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

This should convert the encrypted text back into a plaintext datetime format. Please let us know if this helps you out and please post any further questions you may have.

Sung

edit - copied the wrong "DECLARE" method, the DECLARE should now be correct

How to encryt a Date field?

How do we encrypt/decrypt a datetime field? For e.g. Date of Birth

The way to encrypt any data type that cannot be implicitly converted to varbinary (such as datetime, integers, etc.) is to explicitly convert or cast the value to varbinary.

For example:

CREATE SYMMETRIC KEY key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

DECLARE @.mySecretDate datetime

DECLARE @.mySecretDateBlob varbinary(100)

SET @.mySecretDate = '01/02/2008 12:45PM'

-- Explicitly convert/cast the datetime variable to a varbinary

SET @.mySecretDateBlob = EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.mySecretDate))

SELECT @.mySecretDateBlob as 'datetime ciphertext'

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

go

I hope this information is useful.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul garcia,

How should the date field be stored in the database, should it be datetime or varbinary?

I am inserting data using a stored procedure that encrypts the date using encryption keys. I am using a view to decrypt the date.

Table design

Firstname -- varbinary(50)

secretdatevarbinary(100)

Stored procedure:

DECLARE @.FIRSTNAME varchar(100)

DECLARE @.secretdate datetime

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

insert into tbltest firstname, lastname, secretdate values(

EncryptByKey( key_guid('key_01'), @.FIRSTNAME),

EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.secretdate ))

close symmetic keys

How do i decrypt the date field?

Please help.Thanks in advance

Ks

|||

Hi Ks,

I'm not sure if I understand your question.

Encrypted data will be varbinary so the encrypted text should be stored in a varbinary column. As for decrypting the data, I am copying and pasting Raul's response from before:

DECLARE @.mySecretDateBlob varbinary(100)

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

This should convert the encrypted text back into a plaintext datetime format. Please let us know if this helps you out and please post any further questions you may have.

Sung

edit - copied the wrong "DECLARE" method, the DECLARE should now be correct

How to encorporate Date and Time comparison to pull a field

I'm trying to pull this data in my first inner join trying to pull the rmsbalance based on the latest date/time combination but not having any luck with my syntax because sql is throwing errors due to my syntax below and I can't figure how how to do the check on the date and time combo:

SELECT rf.RMSTRANCDE,

rm.rmsacctnum,

SUM(rf.rmstranamt) AS [Sum Tran Amt],

rf10.rmsbalance

FROM RMASTER rm

INNER JOIN <-- Problem starts in this inner join

(

SELECT RMSFILENUM,

rmsbalance

FROM RFINANL a

where rmstrandte + rmstrantim = (select max(rmstrandte) + max(rmstrantm) from RFINANL)

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance

HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)

AND rf10.rmsbalance <> 0.00

What is the error message? Syntax of the SELECT statement seems fine.|||

thanks, but I actually figured it out on my own

sql

How to enable range Date in Calender?

Hi,

I am using Asp.net Calender in my application and i have two date (i.e StartDate and EndDate). I need to enable dates between these two date in calender.

So the user can choose date with in this set of date.

Can any one give me idea to solve this problem

Thanks,

Hi,

Do you want to pass these dates to a report?

If so, you need to create 2 parameters and pass the start and end date to the report.
If not, you should probably need to post your question in a different forum.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog