Removing the time from the results of GETDATE()

I have been using CONVERT(datetime, CONVERT(varchar,
GETDATE(), 1)) to remove the time portion of the date,
and it has worked reliably for a long time. Due to
changes in the server's environment (it has been moved to
Norway), this statement will no longer work when invoked
from within Access. Is there an alternative method for
doing this that does not risk complications due to
differing date formats?
Any help would be greatly appreciated - the errors this
is causing is wreaking havoc on an application that has,
up to now, run reliably for almost nine years and three
versions of SQL Server!
Daniel Inman
Intelligent Query EnginesThe best date format for international apps is yyyymmdd. Try:
SELECT CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112))
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Some of the ways to get this are :
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112))
SELECT CAST(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112) AS DATETIME)
SELECT CURRENT_TIMESTAMP - {fn CURRENT_TIME}
SELECT CONVERT(DATETIME, {fn CURRENT_DATE()})
SELECT {fn curdate()}
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Hi,
If you want the date in a varchar, use:
select convert(varchar, Getdate(),1)
If you want it as a datetime:
select convert(datetime,convert(varchar, Getdate(),1))
(the time portion will be 00:00:00.000)
Rups|||> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
This is what Daniel was using already, and the problem is that we shouldn't
write code like this as it doesn't work in an international environment:
set language us_english
select convert(datetime,convert(varchar, Getdate(),1))
set language FRENCH
select convert(datetime,convert(varchar, Getdate(),1))
set language us_english
Code 112 should be used (as per the pther posts) as it prodices a "safe"
format: 'yyyymmdd'.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rupesh Kaslay" <rupeshkaslay@.yahoo.com> wrote in message
news:08eb01c3aa7a$28595120$a101280a@.phx.gbl...
> Hi,
> If you want the date in a varchar, use:
> select convert(varchar, Getdate(),1)
> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
> Rups
>