Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!