I'm new to SQL Server 2000 and have a question about DateTime. I'm more comfortable with Access and how it handles dates. I use a # for dates there. Also I never had the actual time in the field either. Now I have a table in SQL Server with date and time. I try to run a query with just date but it doesn't work. How can I convert this field to just date, or what function can I use in my query to just look at the date and ignore the time? Thanks.

All date type fields in SQL have the time in them. You can simply not save the time, or convert the datetime to a char to remove the time from being displayed. Most other databases have a date only field type of some sort but MS-SQL does not.

Play around with this

declare @date datetime
select @date = convert(varchar,getdate(),110) --or select @date = '20051208'
select @date,dateadd(d,1,@date) -- just so that you can see what the date looks like

select * from table
where datefield >= @date
and datefield < dateadd(d,1,@date)



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Hi,

SELECT CONVERT(varchar(10), field1, 103) FROM Table1
SELECT CONVERT(varchar(10), getDate(), 103) FROM Table1

change the style(103) as you want.

If you have any datetime parameters, convert them also
SELECT * FROM table1 WHERE CONVERT(varchar(10), field1, 103) = CONVERT(varchar(10), @Parameter1, 103)

Nalla, If you do a convert on a datetime field you will get a table scan instead of an index seek/scan and that will degrade performance

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

SqlMenace,
Suppose my table name is Table 1 and the field name is Field1 and the values in my field is stored in this format,
2005-03-25 10:17:36.390
I want to select all the records from Table1 where Field1 = '25/03/2005'

so what I'm doing is,

@parameter1 = '25/03/2005'

SELECT * FROM Table1 WHERE CONVERT(varchar(10), Field1, 103) = CONVERT(varchar(10), @Parameter1, 103)

Please tell me how to do this without the CONVERT function or any methods better than this.

Nalla, you can do it like this


declare @parameter1 datetime
select @parameter1= '20050325'
select @parameter1,dateadd(d,1,@parameter1) -- just so that you can see what the date looks like

select * from table
where Field1 >= @parameter1
and Field1 < dateadd(d,1,@parameter1)


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Hi RickP,

Here's a link that discusses different ways of getting just the date part from a datetime value:

http://www.sql-server-helper.com/fun...date-only.aspx

Hope this helps.

SQL Server Helper - FAQ - About Dates
http://www.sql-server-helper.com/faq/dates-p01.aspx