getdate() function

im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql