getting today's data in '2006-01-20' format

20 Jan 2006 9:20 PM

hazz

I would like to use today's date in the format as below ('2006-01-20')
How do I format the result of GetDate() to achieve that? Thank you. -hazz

declare @now datetime
set @now = '2006-01-20'

SET DATEFORMAT ymd;
declare @now datetime
set @now = GetDate()

20 Jan 2006 9:38 PM

Anith Sen

See CONVERT function in SQL Server Books Online.

--
Anith

20 Jan 2006 10:11 PM

hazz

Thank you for reminding me of convert, Anith.

declare @now datetime

set @now = CONVERT(CHAR(10),GETDATE(),110)

works but I would like to have the timestamp show the minutes/hour/sec and not just 00:00:00000000

-hazz

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:ePOMgngHGHA.2300@TK2MSFTNGP15.phx.gbl...
> See CONVERT function in SQL Server Books Online.
>
> --
> Anith
>
>

20 Jan 2006 11:19 PM

Louis Davidson

Best practice is to use the UI to do this, as what you can see that convert
gives you is all that you get formatwise. On the other hand, you can build
your own formatting bits by using the datepart function:

select datepart(hour,getdate())
select datepart(minute,getdate())
select datepart(second,getdate())

Then just cast these to varchar values and go to town :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"hazz" <h***@sonic.net> wrote in message
news:O1ysn4gHGHA.140@TK2MSFTNGP12.phx.gbl...

Thank you for reminding me of convert, Anith.
declare @now datetime
set @now = CONVERT(CHAR(10),GETDATE(),110)
works but I would like to have the timestamp show the minutes/hour/sec and
not just 00:00:00000000

-hazz

Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:ePOMgngHGHA.2300@TK2MSFTNGP15.phx.gbl...
> See CONVERT function in SQL Server Books Online.
>
> --
> Anith
>
>

20 Jan 2006 11:31 PM

hazz

Thank you Louis. I am ready to go to town now! -hazz

Show quote

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:%23ElM0fhHGHA.2320@TK2MSFTNGP11.phx.gbl...
> Best practice is to use the UI to do this, as what you can see that
> convert gives you is all that you get formatwise. On the other hand, you
> can build your own formatting bits by using the datepart function:
>
> select datepart(hour,getdate())
> select datepart(minute,getdate())
> select datepart(second,getdate())
>
> Then just cast these to varchar values and go to town :)
>
> --
> ----------------------------------------------------------------------------
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
>
> "hazz" <h***@sonic.net> wrote in message
> news:O1ysn4gHGHA.140@TK2MSFTNGP12.phx.gbl...
>
> Thank you for reminding me of convert, Anith.
> declare @now datetime
> set @now = CONVERT(CHAR(10),GETDATE(),110)
> works but I would like to have the timestamp show the minutes/hour/sec and
> not just 00:00:00000000
>
> -hazz
>
> "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> news:ePOMgngHGHA.2300@TK2MSFTNGP15.phx.gbl...
>> See CONVERT function in SQL Server Books Online.
>>
>> --
>> Anith
>>
>>
>