Solved

Data formatting:  GETDATE() - 1 day, with time 00:00:00.000

Posted on 2008-06-19
5
1,632 Views
Last Modified: 2008-06-19
I need a function build that returns whatever yesterday was, with the timestamp of 00:00:00.000

i.e. Set @variable =  CONVERT(datetime, getdate(), 101), with time 00:00:00.000 instead of 15:25:55:042

TIA
Jim
0
Comment
Question by:Jim Horn
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 200 total points
ID: 21825989
select cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 50 total points
ID: 21825995
0
 
LVL 3

Expert Comment

by:darkmooink
ID: 21825997
CONVERT(datetime, month(getdate()) + day(getdate()) + year(getdate()), 101)
i think this might work
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 21826041
>select cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
Worked.

>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
This could come in handy.

>CONVERT(datetime, month(getdate()) + day(getdate()) + year(getdate()), 101)
returned 1905-07-27 00:00:00.000
0
 
LVL 3

Expert Comment

by:darkmooink
ID: 21826101
doh sorry
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question