casting date and time - SQL Server Forums

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

SQL Server Development (2000)

casting date and time

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-09-22 : 09:24:39

I have checked the faq's but can't find it anywhere. I need to add two varchar fields mydate and mytime to give me a datetime field.

e.g.

mydate = 29/08/2005
mytime = 8.55

and cast it to give me a datetime field

Its a simplistic cast but when that has exited my head and can't seem to find anywhere.

Your help would be appreciated so I could get on to more complex matters

JP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 09:41:00

You should use Proper DateTime datatype to store Dates
Here is one way

Declare @mydate varchar(12)
Declare @mytime varchar(12)
set @mydate = '20050829'
set @mytime = '8:55'
Select Convert(DateTime,(@mydate +' '+ @myTime))

Madhivanan

Failing to plan is Planning to fail

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-22 : 19:14:09

How about this:

SELECT CONVERT(datetime, STUFF(RIGHT(mydate, 7), 4, 0, LEFT(mydate, 3)) + ' ' + REPLACE(mytime, '.', ':'))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 00:42:41

quote:
Originally posted by nosepicker

How about this:

SELECT CONVERT(datetime, STUFF(RIGHT(mydate, 7), 4, 0, LEFT(mydate, 3)) + ' ' + REPLACE(mytime, '.', ':'))

If Datepart and Time part are exactly
mydate = 29/08/2005
mytime = 8.55

then yours is the way

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-23 : 03:11:14

what's 8.55?
8 hours, 55 minutes or 33 minutes? (.55 hours converted to minutes)

quote:
Originally posted by madhivanan

You should use Proper DateTime datatype to store Dates
Here is one way
Declare @mydate varchar(12)
Declare @mytime varchar(12)
set @mydate = '20050829'
set @mytime = '8:55'
Select Convert(DateTime,(@mydate +' '+ @myTime))

Madhivanan

Failing to plan is Planning to fail

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 03:13:11

>>what's 8.55?

I mean 8 hours and 55 minutes

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-23 : 04:19:55

yeah, i reckon that's what you mean,