extract just the date from a datetime field using T-SQL

I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)

This works, but I thought there must be an easier way.

There are many ways. Easiest is to do below:

convert(varchar, sampletimestamp, 112) = @.selcteddate

|||

Something like this:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

And best to use a form like this for your where:

WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))

So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

Extract hh AM/PM from getdate()

Hi,
I am looking for a query to extract hour and AM or PM value from a date on sql2000.

ex/-
Input : 2001-12-28 22:18:07.810 (from getdate())
Output : 10 PM

select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
substring (convert(varchar, convert(datetime, getdate(),20), 100),
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

The above works but is there a better way to do this?This is a little shorter:

SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END|||thanks for your reply.
but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
Hence to reduce my troubles, i will stick with the good ol' substring.

SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
substring(CONVERT(VARCHAR,getdate(),22), 19,2))

Expressions for parameter values?

Hello all... im trying to figure out... how can i use an expression in a
parameter formula?
I have a report which takes a date parameter. I want to make a subscription
using last week as the date. So for example, i want to set up the
subscription to run every Sunday, and use a value of TODAY() - 7. If i enter
that as a value though, it says its an invalid type. I also tried it with an
equal sign on the front ( =TODAY()-6 ).
Is there a way to do this?
Thanks in advance,
- Arthur Dent.You can use the DateAdd function to add a day/hour/minute/month etc to a
date.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello all... im trying to figure out... how can i use an expression in a
> parameter formula?
> I have a report which takes a date parameter. I want to make a
> subscription using last week as the date. So for example, i want to set up
> the subscription to run every Sunday, and use a value of TODAY() - 7. If i
> enter that as a value though, it says its an invalid type. I also tried it
> with an equal sign on the front ( =TODAY()-6 ).
> Is there a way to do this?
> Thanks in advance,
> - Arthur Dent.
>|||Thanks for the reply. Unfortunately, that doesnt seem to work either. When i
type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
The value provided for the report parameter 'ForWeekOf' is not valid for its
type. (rsReportParameterTypeMismatch)
TIA-
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> You can use the DateAdd function to add a day/hour/minute/month etc to a
> date.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in a
>> parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>|||It sounds like you are trying to type an expression into the date field in
report manager. This won't work - you can only type date constants there.
You should rather load the report in report designer and set the default
value of the date parameter to something like =Today.AddDays(-7). Before
publishing to the report server, make sure to delete the existing report
*before* publishing.
Then, you can create a subscription which will use an expression-based
default value for the date parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. Unfortunately, that doesnt seem to work either. When
> i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
> The value provided for the report parameter 'ForWeekOf' is not valid for
> its type. (rsReportParameterTypeMismatch)
> TIA-
>
> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>|||Ah, yes, that sounds like it would do exactly what i need.
Thanks!!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O69jArtSFHA.2556@.TK2MSFTNGP12.phx.gbl...
> It sounds like you are trying to type an expression into the date field in
> report manager. This won't work - you can only type date constants there.
> You should rather load the report in report designer and set the default
> value of the date parameter to something like =Today.AddDays(-7). Before
> publishing to the report server, make sure to delete the existing report
> *before* publishing.
> Then, you can create a subscription which will use an expression-based
> default value for the date parameter.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the reply. Unfortunately, that doesnt seem to work either.
>> When i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error
>> as so:
>> The value provided for the report parameter 'ForWeekOf' is not valid for
>> its type. (rsReportParameterTypeMismatch)
>> TIA-
>>
>> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
>> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() -
>> 7. If i enter that as a value though, it says its an invalid type. I
>> also tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>>
>

expression to get CreatedBy, CreationDate, ModifiedBy, and ModifiedDate properties for report?

If you look at the General Properties for a report in Report Manager, there are 4 properties listed at the top:

Modified Date: 2/26/2007 6:37 PM Modified By: DOMAIN\username1 Creation Date: 2/14/2006 5:19 PM Created By: DOMAIN\username2

What I'm looking for is if there's a way to retrieve these properties via an expression from a textbox within a report. For instance, there's already an expression that is:

=Globals!ReportName

I know it won't be that easy for the CreatedBy, CreationDate, ModifiedBy, and ModifiedDate properties, but I thought I'd ask if there was a workaround or hack.

We're wanting to put that info into the footer of all our reports. (Actually this is for Report Builder reports, but you can still use expressions in Report Builder reports, so it should apply as it would to normal reports.)

Hi Greg,

I don't believe any of that information is stored in the RDL itself. The only way I know to get that information is to query the ReportServer database, similar to:

SELECT [ModifiedDate]

,[Modified].[UserName]

,[CreationDate]

,[Created].[UserName]

FROM [ReportServer].[dbo].[Catalog]

INNERJOIN [ReportServer].[dbo].[Users] as [Modified] ON [Catalog].[ModifiedByID] = [Modified].[UserID]

INNERJOIN [ReportServer].[dbo].[Users] as [Created] ON [Catalog].[CreatedByID] = [Created].[UserID]

WHERE [Catalog].Name =Globals!ReportName.Value

Then you can access those fields from the dataset.

HTH,

Jessica

Exporting sorted data

Hi,

I have created a crystal report using visual studio. I have three fields in the report - Date, Time and No. of Clients. On loading report is sorted by Date and Time Fields. At run time I sort the report by number of cliets. It shows on Html fine but when i export it to PDF, it shows me the original data sorted by Date and Time. Has anyone run into similar problem ... any help would be appreciated.

Thanks,
DKOpen the report and Uncheck the option Save Data With reports option(from File menu), save the report and try|||hi,

I am using web application to show my crystal report and it does not have the option you told me to change.

I looked into all the options available ... and could not find "Save Data With reports option".

Thanks,
DK|||Hi dsaxena,

I don't think your problem is a "Save Data" problem. It sounds like you've discovered a bug/limitation with the viewer...there are a few.