Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 26th, 2003, 06:24 AM
Authorized User
Join Date: Jul 2003
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need ONLY date in datetime field in Database

Hi there

Can anyone help me with the following:

At the moment I have 2 fields in my database with the Data Type as datetime. My 2 fields are called StartDate and StopDate.

I have an insert page with form validation that only allows a user to enter in a date in the format dd/mm/yyyy.

Originally my SQL statement on my display page read as follows:
Select * from News WHERE StartDate <= getdate() AND StopDate >= DateAdd(day, -1, getdate())

This worked fine but it's not what I want. I only want to compare the date and NOT the time so I changed my statement to:
Select * from News WHERE StartDate <= date() AND StopDate >= date()

which then caused an error because, as i've found out, my datetime Data Type in SQL Server DOES put a 'hidden' time in, even though it's not displayed onscreen.

I want a date field in my SQL database but NOT a time added at all. Even using the Data Type smalldatetime puts a 'hidden' time in my record.

So, my question is this:
Is there anyway of setting up my StartDate and StopDate fields in the database so that it's ONLY a date? I'm wondering if there's something I could put in my stored procedure, or in the Formula section of my column.

Help.........and thanks in advance!


Platform: Windows XP, Dreamweaver MX, Microsoft SQL Enterprise Manager Version: 8.0.

Old November 26th, 2003, 09:37 AM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

Convert to smalldatetime (which has no hour)

...WHERE convert(smalldatetime, YourDateField) = Convert(smalldatetime,DateAdd(dd, -2, Getdate()))

Old November 26th, 2003, 11:13 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

Both of the date/time datatypes in SQL Server represent both a date and a time. The difference is in the precision of the date/time representations. For example, a DateTime can represent a time value to the nearest 3.33 milliseconds, whereas a SmallDateTime can only represent a time to the nearest minute. The range of dates which can be resented is substantially different as well. Note that the prior poster is in error when he states that a smalldatetime has "no hour". It indeed has both hours and minutes.

You cannot avoid dealing with both the date and the time. The time is not "hidden" as you state - it's always there and is always a factor in comparisons as you've seen.

Though you cannot avoid storing the time, you can arrange to ignore it. By default, if you create a DateTime value and do not supply a time, then the time is set to midnight (time 00:00:00.000). You can take advantage of this fact and use the CAST and CONVERT functions to strip off the time. Thus, the following expression:
    CAST(CONVERT(char(8),<datetimeexpression>,112) as datetime)
will take <datetimeexpression> and convert it to a 8 character string in the format 'yyyymmdd'. The CAST function will then take this string and convert it back to a datetime. Since no time value is supplied, the resultant datetime value will have the time set to midnight.

If you consistently set midnight as the time, you can in effect ignore the time entirely. If you really do not care about the time, then you should consider using the above expression to strip off the time when you insert the 'startdate' and 'stopdate' values in your table. Of course, the GetDate() function (or its SQL-92 compliant equivalent CURRENT_TIMESTAMP) returns both the current date and time, so you need to strip off the time appropriately whenever you use it in a comparison expression.

Take care, though. It may be to your future advantage to store the full datetime as your 'startdate' and 'stopdate'. If you discard the time when the value is inserted/updated, you'll never be able to recover it. If you store it, you can ignore it for now, then use it later, should your business requirements change and your boss starts asking you for more precise analysis than to the nearest day...

Jeff Mason
Custom Apps, Inc.
Old November 26th, 2003, 02:15 PM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

Your absolutely right. I should not have posted the message in that format.

Once again thank you.

Old November 26th, 2003, 02:53 PM
Authorized User
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts

sql server yukon will have new datatypes. two of them are: date and time.

link: http://www.microsoft.com/technet/tre...xt/SQLYDBA.asp

look for "Date is the date part of datetime" on the page linked above for more details.

Old April 15th, 2006, 01:06 PM
Registered User
Join Date: Apr 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

hello friends,
              i need help in datetime in MS SQL SERVER 2000.

my initial query is:
select year(datetime) as year, month(datetime) as month,
day(datetime) as day,convert(datetime,(cast(year(datetime) as nvarchar(4))+'-'+cast(month(datetime) as nvarchar(2))
       +'-'+cast(day(datetime) as nvarchar(2))+' '
    +cast(datepart(hour,datetime) as nvarchar(2))+':'+cast(datepart(minute,datetime) as nvarchar(2))+':'
     +cast(datepart(second,datetime) as nvarchar(2))),20) as converted datetime

from weatherdata
where [datetime2] is null

year month day converteddatetime
2005 1 2 2005-01-02 05:56:00.000

i do not want the minutes and seconds in the converteddatetime and i am not able to run it.
if i take the minutes and seconds statmenets in the query
"(datepart(minute,datetime) as nvarchar(2))+':'+cast(datepart(second,datetime) as nvarchar(2))),"
it is not working.

so please friends could you help me out in this query?

i want the output to be as below:
year month day converteddatetime
2005 1 2 2005-01-02 05:00:00.000


Old April 29th, 2007, 11:04 AM
Registered User
Join Date: Apr 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

This worked for me:

CONVERT(varchar(10), [THEDATE], 101)as THEDATE

Old April 29th, 2007, 01:29 PM
Friend of Wrox
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts


Old April 30th, 2007, 06:16 AM
gog gog is offline
Registered User
Join Date: Apr 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

CONVERT (varchar, GetDate(), 103) as theDATE

0 - Jan 25 2002 3:12PM
1 - 01/25/02
2 - 02.01.25
3 - 25/01/02
4 - 25.01.02
5 - 25-01-02
6 - 25 Jan 02
7 - 25 Jan 02
8 - 15:02:02
9 - Jan 25 2002 3:02:34:070PM
10 - 01-25-02
11 - 02/01/25
12 - 020125
13 - 25 Jan 2002 15:10:17:250
14 - 15:10:46:297
20 - 2002-01-25 15:11:51
21 - 2002-01-25 15:12:11.073
101 - 01/25/2002
102 - 2002.01.25
103 - 25/01/2002
104 - 25.01.2002
105 - 25-01-2002
106 - 25 Jan 2002
107 - Jan 25, 2002
108 - 15:18:22
110 - 01-25-2002
111 - 2002/01/25
112 - 20020125
114 - 15:19:51:113
126 - 2002-01-25T15:20:12.177
131 - 12/11/1422 3:21:05:673PM

Old April 30th, 2007, 09:06 AM
Authorized User
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts

Here's a couple of links that may help you:


SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Use DateTime Field kcsathish ASP.NET 2.0 Professional 0 July 15th, 2008 01:01 AM
Query a Sql Database Date Field feets Access VBA 3 October 18th, 2007 10:10 AM
Compare only the date portion of a datetime field CricketMaster Access 6 April 27th, 2005 01:06 AM
Seperating Date part from a datetime field ctranjith SQL Server 2000 2 October 25th, 2004 06:42 AM
Query DateTime Field mtalam Access 5 June 3rd, 2004 06:51 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.