Date Conversion in T-SQL -Immediate help needed!!!

Posted on 2001-08-29
Last Modified: 2010-05-18
This should be an easy question - but I need immediate help...

Here is the logic behind piece of SQL I am trying to run in SQL7

FROM   ExportLog
WHERE  TRUNC(ExportTimeStamp) = '01/01/2001'

Well TRUNC is a command in ORACLE that strips the time part of the argument.  Of course there is no TRUNC command in SQL7.  How do people do this in T-SQL??

is there some kind of TO_DATE or FORMAT function I can use on the date?  i.e:

FROM ExportLog
WHERE Format(ExportTimeStamp, 'yyyymmdd') = '20010101'

Thanks for your help
Question by:jeffstatt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 18

Expert Comment

ID: 6436155
where convert(datetime,convert(varchar(13),ExportTimeStamp,113)) = '1 jan 2001'

LVL 18

Expert Comment

ID: 6436170
The above does the comparison in a date datatype.
If you wanted to use '20010101' as character then you could

replace(convert(varchar(10),ExportTimeStamp,121),'-','') = '20010101'

Accepted Solution

ibro earned 300 total points
ID: 6436174
FROM ExportLog
WHERE convert(varchar,ExportTimeStamp,101) = '01/01/2001'

101 means date is in format 'mm/dd/yyyy'

see convert help in BOL for more date formats

Author Comment

ID: 6436195
That's the one I needed! Thanks

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 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