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!

Lucy

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

Convert to smalldatetime (which has no hour)

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

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:
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.
www.custom-apps.com

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

Once again thank you.

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.

defiant.

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

output:
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

thanks,
raj.

This worked for me:

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

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

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

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

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

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

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