Join date & time in expression

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

SQL Server 2000 Forums

Transact-SQL (2000)

Join date & time in expression

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-30 : 09:00:39

Hello,

This follows on from a date-join question i posted & recieved some really good help with yesterday.

I'm now dealing with a time field stored as a nvarchar (8) and a date stored as a datetime exampled below;

Date
22/08/2008

time
13:53:00

what expression can i write to join them together?

(I have tried writing some sample code but it wont allow me do put the dates in the format i see)

Thanks
Dan

---------------------------------------------
/*
Please paste T-SQL into query window

THIS T-SQL WILL NOT RUN AS THE DATE SAMPLE DATA DATES
ARE OUT OF RANGE - I JUST WNAT TO SHOW WHAT MY DATES LOOK LIKE

*/
---------------------------------------------
CREATE TABLE #table1

(
MyDate [datetime] NULL,
MyTime nvarchar(8)
)

GO
SET ANSI_PADDING OFF

Insert into #table1
select '22/12/2008','13:33:00' UNION all
select '23/11/2008','17:25:00' UNION all
select '18/09/2008','21:09:00'

select * from #table1

drop table #table1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:04:36

SELECT MyDate, MyTime, MyDate + MyTime AS MyNewCombinedDateTime
FROM #Table1

E 12°55'05.63"
N 56°04'39.26"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-30 : 09:12:28

without wanting to win the award for posting the easiest question ever...
I did try this and it would not work.

However - in trusting your judgment i loaded it into T-SQL and it ran! - i just didn’t work in 'enterprise manager' which is where most of my T-SQL starts its life.

thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:15:50

Try this in EM

SELECT	MyDate,
	MyTime,
	MyDate + CAST(MyTime AS DATETIME) AS MyNewCombinedDateTime
FROM	#Table1

E 12°55'05.63"
N 56°04'39.26"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-30 : 09:23:44

If you can change your table design, use proper data types and do not store your TIMES as a VARCHAR -- use DateTime. Then, you can simply add them together (without casting or converting) to produce a DateTime, and you will be sure that your data contains only valid times.

More here:
http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

(Sorry for all the blog SPAM but this is a topic I cover a LOT !)

- Jeff
http://weblogs.sqlteam.com/JeffS

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-30 : 09:44:14

when i convert the nvarchar(8) (e.g. data: '12:40:00') to a datetime it comes out like this;

01/01/1900 12:40:00

is this what you meant - so i can add without the cast \ converting.

Thanks

(PS: Thanks Peso - works in EM!)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-30 : 10:10:00

Yes -- a date time at 1/1/1900 is the same as not having a date at all. Think of a datetime like a decimal:

0.0

where the first zero is the date, and the second zero is the time. The DateTime equivalent of 0.0 is:

1/1/1900 12:00:00 AM

So, if you add that to any other DateTime, the result is the same as adding 0.0 to any number -- it doesn't change. Thus, if you add:

1/1/1900 8:00 AM

to any DateTime, that is the equivalent of adding 8 hours to that DateTime.

Read the articles I posted for more on all this.

- Jeff
http://weblogs.sqlteam.com/JeffS

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-30 : 10:19:19

Thanks Jeff,
I have been making my way through the articles;

esp.
"Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME "

has helped me look at date and times in a new light!

Thanks for everyone’s help - job done ... for now i think :-)

annu84
Starting Member

3 Posts

Posted - 2008-11-12 : 21:48:12

hi ..... i would like 2 do the same thing as in join a date and a time field into a datetime field...but not in application but in SSIS.

I HAVE A DERIVED COLUMN ...its expression is :
(DT_DATE)(DT_STR,20,1252)thedate + " " + (DT_STR,20,1252)thetime

exp: field 1(date): 01-01-2008
field 2(time): 02:30:50

but this only give output as : 01-01-2008 00:00:00
what i want is : 01-01-2008 02:30:50

how do i do dis...pls help anyone.i cant use datepart function here cz it only works in a datetime field not date alone /time alone type...

thanx a bunch
anu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 01:52:21

are thedate & thetime variables? if yes you need to use @[User::variablename]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources