July 4, 2008 01:07pm pdt

1. chapmandew 1,559,115
2. angelIII 1,006,072
3. aneeshat... 456,385
4. mark_wills 330,136
5. ee_rlee 313,495
6. dtodd 204,505
7. brejk 181,605
8. ptjcb 157,919
9. Thomasian 149,392
10. dportas 107,990
11. PFrog 106,660
12. sbagireddi 100,572
13. dqmq 96,730
14. rickchild 94,645
15. momi_sabag 94,339

1. angelIII 2,286,263
2. chapmandew 1,559,115
3. aneeshat... 1,021,490
4. ptjcb 523,311
5. Yveau 511,323
6. dtodd 415,510
7. PFrog 376,688
8. dqmq 368,078
9. mark_wills 330,136
10. imitchie 329,350
11. appari 318,736
12. ee_rlee 313,495
13. Lowfatsp... 222,289
14. matthews... 199,052
15. rboyd56 194,590

03.06.2008 at 02:29PM PST, ID: 23221360

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.9

Get Time by Subtracting in SQL Query

Zones: SQL Server 2005, Databases Miscellaneous, MS SQL Server

Tags: SQLServer 2005

IN MS Access I have a query to get time
AS
SELECT Now()-[intake_dt] AS [Time] FROM Table

How do I do that in SQLServer 2005

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: intikhabk

Solution Provided By: chapmandew

Participating Experts: 5

Solution Grade: B

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

03.06.2008 at 02:36PM PST, ID: 21065499

Rank: Sage

matthewspatrick:

SELECT GETDATE() - intake_dt AS [Time]
FROM SomeTable

03.06.2008 at 03:03PM PST, ID: 21065695

Rank: Genius

chapmandew:

The correct way to do this in SQL is

SELECT DATEDIFF(n, intake_dt, GETDATE()) as DiffInMinutes FROM Table

Accepted Solution

03.06.2008 at 03:04PM PST, ID: 21065707

Rank: Master

dready:

SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime]
Here, you should replace getdate() with the name of you datecolumn:

SELECT CONVERT(VARCHAR(10), dateField, 108) from myTable.

From BOL:
To get just the time part of a DateTime data type, the CONVERT function can be used. The CONVERT function explicitly converts an expression of one data type to another. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression is any valid Microsoft® SQL Server" expression. data_type is the target system-supplied data type. length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types. Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To get the time part of a datetime data type, the style to use is 108, which will format the datetime expression to HH:MI:SS format.

SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime]
If you want to include the millisecond (HH:MI:SS.MMM), the style to use is 114:

SELECT CONVERT(VARCHAR(13), GETDATE(), 114) AS [CurrentTime]
In both cases, the returned time part is of varchar data type. If you want to return the time part as a datetime data type, a CAST needs to be performed:

SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 108) AS DATETIME) AS [CurrentTime]
SELECT CAST(CONVERT(VARCHAR(13), GETDATE(), 114) AS DATETIME) AS [CurrentTime]
Casting the time part to a datetime data type will include a date part with a value of 01/01/1900 in the result. Here's an example:

CurrentTime
-------------------------
1900-01-01 09:30:25.000

03.06.2008 at 03:06PM PST, ID: 21065718

Rank: Genius

chapmandew:

I dont' think that is what the author is asking....

03.06.2008 at 06:19PM PST, ID: 21066742

Rank: Sage

matthewspatrick:

chapmandew said:
>>The correct way to do this in SQL is
>>
>>SELECT DATEDIFF(n, intake_dt, GETDATE()) as DiffInMinutes FROM Table

There is not a single correct way to do it; it depends on what, ultimately, the Asker really needs
to do.

My suggestion will exactly mirror the behavior s/he is used to seeing in Access.

Regards,

Patrick

03.06.2008 at 06:50PM PST, ID: 21066918

Rank: Genius

chapmandew:

Your method may work the same as Access....but, my comment on the "correct way to do it" was because your way will only subtract a day at a time....with my method, you can specify how you want to subtract the time...you know what I mean? Didn't mean to be brash about it, I just like to define how the dates are subtracted from each other whenever possible.

03.06.2008 at 07:09PM PST, ID: 21067026

Rank: Sage

matthewspatrick:

chapmandew said:
>>but, my comment on the "correct way to do it" was because your way will only subtract a
>>day at a time

Not so. And BTW, I did not think you were unduly brash :)

The method I showed will not subtract just one day at a time. For example, I just ran this in Query
Analyzer:

select getdate()-convert(datetime, '2008-03-07 00:00:00'), '12am'
union
select getdate()-convert(datetime, '2008-03-07 02:00:00'), '2am'
union
select getdate()-convert(datetime, '2008-03-07 04:00:00'), '4am'

(approximate time was 10pm, EST)

The result:

1899-12-31 18:04:28.343            4am
1899-12-31 20:04:28.343            2am
1899-12-31 22:04:28.343            12am

As you see, there is no "subtracting one day at a time"--the difference between the two datetime values
is computed, and then itself rendered as a date.

Or am I misinterpreting your comment? If so, please let me know, so that I can understand it better.

That said, depending on just what it is intikhabk intends to do, I agree that your method may well be
better suited.

Patrick

03.06.2008 at 07:25PM PST, ID: 21067093

Rank: Genius

chapmandew:

Sure...it will subtract whatever date and time is in the field.

Out of curiosity, is the date on your machine funky, or am I missing something? Todays date - a few hours doesn't seem like it should yield 12/31/1899....

03.06.2008 at 07:50PM PST, ID: 21067216

Rank: Sage

matthewspatrick:

chapmandew said:
>>Out of curiosity, is the date on your machine funky, or am I missing something? Todays date -
>>a few hours doesn't seem like it should yield 12/31/1899....

Nothing unusual about my date/time settings. It has to do with how SQL Server stores dates, and
how it carries out date arithmetic. Tr it yourself on your own computer, and you'll get similar
results :)

03.06.2008 at 07:59PM PST, ID: 21067244

Rank: Genius

chapmandew:

I did....but I guess I was just expecting it subtract a few hours...

03.06.2008 at 08:06PM PST, ID: 21067261

Rank: Wizard

ee_rlee:

>Now()-[intake_dt]

hi, in access i think that returns the difference in days. try this

Datediff(second,[intake_dt],Now)/86400.0 AS [Time]

03.06.2008 at 08:11PM PST, ID: 21067277

Rank: Sage

matthewspatrick:

chapmandew said:
>>I did....but I guess I was just expecting it subtract a few hours...

The result *is* a few hours :)

It's an artifact of how date arithmetic is handled.

03.06.2008 at 08:13PM PST, ID: 21067287

Rank: Sage

matthewspatrick:

ee_rlee said:
>>>Now()-[intake_dt]
>>
>>hi, in access i think that returns the difference in days. try this
>>
>>Datediff(second,[intake_dt],Now)/86400.0 AS [Time]

No offense, but how does this add to the discussion? chapmandrew suggested a DATEDIFF approach
roughly five hours before you posted...

03.06.2008 at 08:15PM PST, ID: 21067297

Rank: Genius

chapmandew:

I gotcha...so, how would you calculate the exact time (today) 11 hours ago?

03.06.2008 at 08:22PM PST, ID: 21067333

Rank: Sage

matthewspatrick:

chapmandew said:
>>I gotcha...so, how would you calculate the exact time (today) 11 hours ago?

In T-SQL, I'd probably do this:

SELECT DATEADD(hh, -11, GETDATE())

03.06.2008 at 08:26PM PST, ID: 21067350

Rank: Wizard

ee_rlee:

@matthewspatrick:

We are trying to replicate a query in Access to SQL server. As I said, subtracting time in access seems to return a numeric value so datediff should be the right approach. But using datediff function (in chapandew's code) return the result in minutes while the original query returns it in days. So I added a /86400.0 to return the value in days.

03.06.2008 at 08:27PM PST, ID: 21067353

Rank: Genius

chapmandew:

OK. :)

03.06.2008 at 08:28PM PST, ID: 21067362

Rank: Genius

chapmandew:

I just returned it in minutes to show the granularity of what you can do with it....could have changed it to milliseconds or hours....

03.06.2008 at 08:48PM PST, ID: 21067437

Rank: Genius

acperkins:

We can only hope this thread does not end up like the following two abandoned questions:
1 08/02/2007 500 An error has occurred during report processin& Open SQL Server 2& &
2 05/14/2007 500 Access to the Web site is blocked by your admi& Open Microsoft IIS &

20080236-EE-VQP-29 / EE_QW_2_20070628