Smalldatetime problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Smalldatetime problem

Author

Topic

birre69
Starting Member

5 Posts

Posted - 2007-03-29 : 10:46:38

I'm having a very nasty problem....
So i'm working with Visual Studio 2005 Framework 2.0. I'm using asp to check if the smalldatetime in the column UitDienst_920 isn't later than the current date.

This is what i'm doing in my asp.net page :

Dim vandaag As Date = CDate(Format(Date.Now, "dd/MM/yyyy HH:mm:ss"))myCommand.Parameters.Add(New SqlParameter("@Today", SqlDbType.VarChar, 100)).Value = Convert.ToDateTime(vandaag).ToShortDateString()
dvWerknemers.DataSource = myCommand.ExecuteReader

this is what's happening in my stored procedure :

ALTER PROCEDURE [dbo].[GetWerknemers]
@TotalRecords int output,
@UserId varchar (20),
@Wildcard varchar(40),
@SearchFrom varchar (40),
@Sort varchar(15),
@Where varchar(50),
@WgId int,
@InDienst int,
@Today varchar(100)
AS

SET NOCOUNT ON
--Aanmaken tijdelijke tabel
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
NrWn_920 int,
Naam_920 varchar(65),
Statuut_920 char(1),
Afdeling_920 varchar(10)
)

DECLARE @SQL varchar(3000)
SET @SQL = ' INSERT INTO #TempTable (NrWn_920, Naam_920, Statuut_920, Afdeling_920)'
+ ' SELECT NrWn_920, Naam_920 + '' '' + Voornaam_920 as Naam , Statuut_920, Afdeling_920'
+ ' FROM WebWerknemer_920'
+ ' WHERE ' + @Where + ' >= ''' + @SearchFrom + ''' AND ' + @Where + ' LIKE ''%' + @Wildcard + '%'' AND ('

IF (@InDienst = 1)
BEGIN
SET @SQL = @SQL + 'UitDienst_920 < '+ @Today + ') AND ('
END

DECLARE WebWerknemer CURSOR
READ_ONLY
FOR SELECT NrWn_920, IntNrWg_920
FROM WebWerknemer_920
WHERE IntNrWg_920 = @WgId

DECLARE @NrWn integer
DECLARE @IntNrWg integer
DECLARE @I integer
SET @I = 0

OPEN WebWerknemer
FETCH NEXT FROM WebWerknemer INTO @NrWn, @IntNrWg
WHILE (@@fetch_status <> -1)

BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @I <> 1
BEGIN
SET @SQL = @SQL + '(NrWn_920 = ' + convert(varchar, @NrWn) + ')'
END
ELSE
BEGIN
SET @SQL = @SQL + ' OR (NrWn_920 = ' + convert(varchar, @NrWn) + ')'
END
END
SET @I = 1
FETCH NEXT FROM WebWerknemer INTO @NrWn, @IntNrWg
END
SET @SQL = @SQL + ') ORDER BY ' + @Sort
PRINT @SQL
CLOSE WebWerknemer
DEALLOCATE WebWerknemer
EXEC (@SQL)

SELECT * FROM #TempTable
SELECT @TotalRecords = COUNT(*) FROM #TempTable

all parameters are good, everything is working fine... just when it's going into the if with the bold, it messes up. It doesn't just show the records where the smalldatetime of UitDienst_920 is smaller than the current date :(

please help!

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 10:50:58

1) Use YYYYMMDD datetime format in @Today string
2) SET @SQL = @SQL + 'UitDienst_920 < ''' + @Today + ''') AND ('

You have to remember that all this is because you have chosen to use dynamic SQL.

Peter Larsson
Helsingborg, Sweden

birre69
Starting Member

5 Posts

Posted - 2007-03-29 : 11:03:17

It works now!

thank you very much

the use of smalldatetimes is weird

. In db it shows dd/MM/yyyy HH:mm:ss , but you have to compare yyyyMMdd?

thank you so much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 11:06:27

It is the SQL Server who doesn't know if "12/07/2007" is either December 7 or July 12.
If you use ISO dateformat YYYYMMDD, SQL Server always know.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-02 : 03:41:37

For more info on date handling refer this
http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources