Date and Time in the same column [Solved]

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Date and Time in the same column [Solved]

Author

Topic

grayish
Starting Member

3 Posts

Posted - 2013-10-24 : 15:07:05

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-24 : 15:09:33

[code]WHERE DateTimeColumn >= '20131024 AND DateTimeColumn < '20131025'-- Get all rows for today, October 24th 2013.[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 15:27:44

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);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 02:30:31

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-28 : 05:52:24

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

grayish
Starting Member

3 Posts

Posted - 2013-10-31 : 19:37:23

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

grayish
Starting Member

3 Posts

Posted - 2013-10-31 : 19:40:40

How do I mark this topic as "Solved"?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-01 : 06:50:41

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources