Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date and Time in the same column [Solved]
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

grayish
Starting Member

3 Posts

Posted - 10/24/2013 :  15:07:05  Show Profile  Reply with Quote
Hello SQLteam!

Thanks in advance for your time!

I have a column of data that is in this format (quotes to communicate it is in one cell if viewed in excel):
"8/18/2013 23:58"

Essentially, I have the date and time in one entry.... how do I go about querying, using SQL, the following?

- all data that is less than a specific date (I don't care about time)?
- differences in time (subtracting time, while not caring about date)?

I can do it if the date and time were in different columns, but they are together and I am stuck.


Thanks so much in advance!

Best,

Grayish


P.S. Oh! I am a SQL noob





Edited by - grayish on 11/05/2013 18:12:45

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 10/24/2013 :  15:09:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WHERE DateTimeColumn >= '20131024 AND DateTimeColumn < '20131025'-- Get all rows for today, October 24th 2013.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 10/24/2013 :  15:27:44  Show Profile  Reply with Quote
For finding the difference in time, there are at least couple of different ways - for example see below.
DECLARE @t1 datetime, @t2 DATETIME;
SET @t1 = '20130731 11:17:24';
SET @t2 = '20131021 10:11:13';
SELECT CAST(CAST(CAST(@t1 AS time) AS DATETIME) -CAST(CAST(@t2 AS time) AS DATETIME) AS time);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/25/2013 :  02:30:31  Show Profile  Reply with Quote
quote:
Originally posted by grayish

Hello SQLteam!

Thanks in advance for your time!

I have a column of data that is in this format (quotes to communicate it is in one cell if viewed in excel):
"8/18/2013 23:58"

Essentially, I have the date and time in one entry.... how do I go about querying, using SQL, the following?

- all data that is less than a specific date (I don't care about time)?
- differences in time (subtracting time, while not caring about date)?

I can do it if the date and time were in different columns, but they are together and I am stuck.


Thanks so much in advance!

Best,

Grayish


P.S. Oh! I am a SQL noob







see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 10/28/2013 :  05:52:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
For versions prior to 2008, time difference can be done with


DECLARE @t1 datetime, @t2 DATETIME;
SET @t1 = '20130731 11:17:24';
SET @t2 = '20131021 10:11:13';
SELECT CAST(CONVERT(char(8),@t1,108) AS DATETIME) -CAST(CONVERT(char(8),@t2,108) AS DATETIME) ;


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

grayish
Starting Member

3 Posts

Posted - 10/31/2013 :  19:37:23  Show Profile  Reply with Quote
Hello fellow SQL Team contributors,

Thanks so much for your solutions to my problem! I used a little bit of everyone's suggestions and was able to solve my problem.

Thanks again for your time and help. I really appreciate it!

Best,

Grayish
Go to Top of Page

grayish
Starting Member

3 Posts

Posted - 10/31/2013 :  19:40:40  Show Profile  Reply with Quote
How do I mark this topic as "Solved"?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 11/01/2013 :  06:50:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by grayish

How do I mark this topic as "Solved"?


The only way is to edit the topic and append [SOLVED] at end

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000