SQL Server Forums
Profile | Register | 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]
 New Topic  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
30241 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

3649 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
52317 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
22755 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
22755 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000