good practice?

A couple of questions.
1)
I have a column Event_End_Date with smalldatetime data type. I also have
following update scripts that gets executed every day. Basically I do not
care about the time portion of smalldatetime (or rather it needs to be
default time format 00:00:00)
UPDATE tblEvents
SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
Now is it more proper to program it following way? I know in other
programming language like Java, C++, you would always have to explicitly
cast it to the appropriate data type. How sensitive do I need to be when it
comes to casting in TSQL?
UPDATE tblEvents
SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIME)
2) I have read somewhere that the following query needs to be rewritten
SELECT *
FROM tblEvents
WHERE Event_End_Date = '6/7/2006'
to
SELECT *
FROM tblEvents
WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Of course, should there be an index Event_End_Date, it is useless with the
above query. What's your opinion on this matter relating to datetime data
types?
Thanks all.Justin,
If you use style 112, you do not have to worry about casting because sql
server will always interprets the value correctly as a datetime value, not
matter what language or date format is using your server.
UPDATE tblEvents
SET Event_End_Date = CONVERT(varchar(8), GETDATE(), 112)
If you do not pass the time portion from the client application, sql server
will default it to 00:00:00.000

> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
Not really. If the values in the column has time portion equal to
"00:00:00.000", then it will work without problems. If the values in the
column include tiem portion other that 12 AM, then you should use the patter
n:
...
where
Event_End_Date >= convert(varchar(8), @.d, 112)
Event_End_Date < dateadd(day, 1, convert(varchar(8), @.d, 112))
This way, sql server will make a proper use of an index if this exists. When
you manipulate the column in the "where" clause, sql server does not conside
r
the expresion as a search argument.
Example:
..
where
Event_End_Date >= '20060607'
Event_End_Date < '20060608'
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
AMB
"Justin" wrote:

> A couple of questions.
> 1)
> I have a column Event_End_Date with smalldatetime data type. I also have
> following update scripts that gets executed every day. Basically I do not
> care about the time portion of smalldatetime (or rather it needs to be
> default time format 00:00:00)
> UPDATE tblEvents
> SET Event_End_Date = CONVERT(VARCHAR, GETDATE(), 101)
> Now is it more proper to program it following way? I know in other
> programming language like Java, C++, you would always have to explicitly
> cast it to the appropriate data type. How sensitive do I need to be when
it
> comes to casting in TSQL?
> UPDATE tblEvents
> SET Event_End_Date = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS SMALLDATETIM
E)
> 2) I have read somewhere that the following query needs to be rewritten
> SELECT *
> FROM tblEvents
> WHERE Event_End_Date = '6/7/2006'
> to
> SELECT *
> FROM tblEvents
> WHERE DATEPART(YEAR, Event_End_Date) = 2006 AND DATEPART(MONTH,
> Event_End_Date) = 6 AND DATEPART(Day, Event_End_Date) = 7
> Of course, should there be an index Event_End_Date, it is useless with the
> above query. What's your opinion on this matter relating to datetime data
> types?
> Thanks all.
>
>