Today's Date into Gridview DataSource 1

[djs45uk]

Morning all

I'm using the following data source to retrieve some data from a database:

<asp:SqlDataSource ID="ds_downloads" runat="server" ConnectionString="<%$ ConnectionStrings:StPaulsDatabase %>"
ProviderName="<%$ ConnectionStrings:StPaulsDatabase.ProviderName %>" SelectCommand="SELECT downloadID, downloadTitle, downloadFilename, downloadFileSize, downloadFileType, downloadAddedOn, downloadShowFrom, downloadShowUntil, downloadCategory, downloadStatus FROM Downloads WHERE (downloadShowFrom <= CONVERT (DATETIME, '15/08/2006', 102)) AND (downloadShowUntil >= CONVERT (DATETIME, '15/08/2006', 102) OR downloadShowUntil IS NULL) AND (downloadCategory = @category) AND (downloadStatus = 1)">
<SelectParameters>
<asp:QueryStringParameter Name="category" QueryStringField="category" />
</SelectParameters>
</asp:SqlDataSource>

I would like to replace the two dates with today's date dynamically, e.g. date() - or at least that's what I tried using - both directly in the SQL statement and as parameters with default values. Neither ways worked though.

Any advice is much appreciated!

Dan

 

[djs45uk]

Actually please forget the code I put above. It doesn't work because I wrote the date the wrong way. This is how my code looks:

SELECT     downloadID, downloadTitle, downloadFilename, downloadFileSize, downloadFileType, downloadAddedOn, downloadShowFrom, downloadShowUntil, 
                      downloadCategory, downloadStatus
FROM         Downloads
WHERE     (downloadShowFrom <= CONVERT(DATETIME, '2006-08-15', 102)) AND (downloadShowUntil >= CONVERT(DATETIME, '2006-08-15', 102) OR
                      downloadShowUntil IS NULL) AND (downloadCategory = @category) AND (downloadStatus = 1)

DS

 

[ca8msm]

Try using:

WHERE (datefield < { fn NOW() })

or ask in the SQL Server forum for more info.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Actually, try using GETDATE if you dont want to inclide the time e.g.

WHERE (datefield < GETDATE())

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jbenson001]

For SQL Server, use GetDate() in your sql statment to get the current date and time. It will include the time, but there are many ways in sql to remove the time if needed.

Jim

 

[djs45uk]

Brilliant thank you very much!!

GetDate() does work perfectly.

I'm sorry I should have put this in the SQL forum but I was thinking I'd have to get ASPX to put the date into the sQL. I know for next time!

Thank you both.

Daniel

 

[jbenson001]

glad you got it working

Jim