Concantenating Dates

I am transferring data between DB's. One has DATE and TIME as two individual fields. I need to concantenate them into a single DATETIME field. I am sure the solution is simple but I can't figure it out.
Any advice?
Thanks!If you select from your time column, you should see that it's date is set to January 1st, 1900, like this:
1900-01-01 14:59:27.293

Your date column should show a date as of midnight like this:
2003-07-14 00:00:00.000

If this is the case, you can just add these value together to concatenate them:
select @.Yourdate + @.Yourtime

If this is not the case, you will need to concatenate them as formatted strings and then cast or convert the result to a datetime value.

blindman

computing for the number of hours from 2 date attributes

Hi,

I want to build a calculated fields that compute for the number of hours between 2 date attributes.

let's say I have 2 dates:

1/1/2007 15:30

1/2/2007 9:10

It will give me 18 hours.

cherriesh

Try

VBA!DateDiff('h', DateValue1, DateValue2)

|||

Depend where you want ir and the importance for your project...

You can do that calculation inside a report, in a cube... or you can create it using SQL statment in a named calculation in teh datasourceview in Analysis Services...

helped?

Regards!

|||

Problem resolved?

regards!!

Computing Dates

I'm soliciting any suggestions from the readers of the group on how
best to tackle this task. I need to determine if trouble tickets
opened get closed within 4 hours. Yes, I already know about
datediff(hh, date1, date2, but it's not that simple. The thing I'm
struggle with is that I need to take inaccount business hours (8:00am
to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
10/28/05 10:00am. Any suggestions are much appreciated.Try,
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
else
'more than 4 hours'
AMB
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>|||Correction:
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 20) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Try,
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
> else
> 'more than 4 hours'
>
> AMB
> "BobD" wrote:
>|||Correction: (sorry guys)
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 16) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Correction:
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 20) <= 4)
> print 'may be in 4 hours'
> else
> 'more than 4 hours'
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Bob,
Try this:
SELECT
CASE WHEN cast(convert(varchar(12),date1,114) as datetime) < '08:00'
THEN dateadd(day,datediff(day,0,date1),'12:00
')
WHEN cast(convert(varchar(12),date1,114) as datetime) < '12:00'
THEN dateadd(hour,4,date1)
ELSE dateadd(hour,20,date1) END
FROM yourTable
Steve Kass
Drew University
BobD wrote:

>I'm soliciting any suggestions from the readers of the group on how
>best to tackle this task. I need to determine if trouble tickets
>opened get closed within 4 hours. Yes, I already know about
>datediff(hh, date1, date2, but it's not that simple. The thing I'm
>struggle with is that I need to take inaccount business hours (8:00am
>to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
>10/28/05 10:00am. Any suggestions are much appreciated.
>
>|||Bod
try this
SELECT CASE WHEN DATE1 = DATE2 THEN DATEDIFF(HOUR, DATE1,DATE2) ELSE
DATEDIFF(HOUR, DATE1,(DATEADD(HOUR,-16,DATE2)))
--
Regards
R.D
--Knowledge gets doubled when shared
"BobD" wrote:

> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>

Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
===================
ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
================================================== =========
You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================
> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay =
> (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth =
> (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ================================================== =========
>
|||BTW and FWIW, I added this exact example to the article.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
sqlsql

Computed 'Days to go' Column

Given a column of dates, I would like to create a computed column showing
how many days from the current date until that date (ignoring the year) next
occurs.
E.G. given 3 rows:
DateID StartDate
1 2 January 1954
2 1 March 1978
3 30 December 2001
if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 59
3 31 December 2001 364
and on 1st Jan 2008 (leap year):
DateID StartDate DaysToGo
1 2 January 1954 1
2 1 March 1978 60
3 31 December 2001 365
I have a stored procedure that does the calculation correctly (I think ;),
however it requires parameters, and I need a computed column or view. ANy
help much appreciated
TIA,
Paul Bryant
=================== ALTER PROCEDURE DaysToGo
@.DateID int
AS
DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
SET @.OldDay =
(SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SET @.OldMonth =
(SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
FROM tblDates
WHERE tblDates.DateID = @.DateID)
SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
AS NVARCHAR)
IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
ELSE
SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
===========================================================You could use a calendar table for this... just populate it as far out as
you need, then you can add DATEADD(YEAR, YEAR(GETDATE()), '19540102') to get
January 2, 1954, then take the datediff in days between today and that date.
To see how to populate the calendar table:
http://www.aspfaq.com/2519
If you decide to go that route, we can help you with more specific code.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Paul Bryant" <paul@.gap66.com> wrote in message
news:%23x%23t0jlREHA.2520@.TK2MSFTNGP11.phx.gbl...
> Given a column of dates, I would like to create a computed column showing
> how many days from the current date until that date (ignoring the year)
> next
> occurs.
> E.G. given 3 rows:
> DateID StartDate
> 1 2 January 1954
> 2 1 March 1978
> 3 30 December 2001
> if today is 1st Jan 2005 (non-leap year) I would like a resultset like:
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 59
> 3 31 December 2001 364
> and on 1st Jan 2008 (leap year):
> DateID StartDate DaysToGo
> 1 2 January 1954 1
> 2 1 March 1978 60
> 3 31 December 2001 365
> I have a stored procedure that does the calculation correctly (I think ;),
> however it requires parameters, and I need a computed column or view. ANy
> help much appreciated
> TIA,
> Paul Bryant
> ===================> ALTER PROCEDURE DaysToGo
> @.DateID int
> AS
> DECLARE @.OldDay nvarchar(2), @.OldMonth nvarchar(20), @.NextDate DateTime
> SET @.OldDay => (SELECT CAST(DATEPART(d, tblDates.StartDate) AS NVARCHAR) AS OldDay
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SET @.OldMonth => (SELECT DATENAME(m,tblDates.StartDate) AS OldMonth
> FROM tblDates
> WHERE tblDates.DateID = @.DateID)
> SELECT @.NextDate = @.OldDay + ' ' + @.OldMonth + ', ' + CAST(YEAR(GETDATE())
> AS NVARCHAR)
> IF DATEDIFF(d, GETDATE(), @.NextDate) < 0
> SELECT DATEDIFF(d, GETDATE(), DATEADD(yy, 1, @.NextDate)) AS DaysToGo
> ELSE
> SELECT DATEDIFF(d, GETDATE(), @.NextDate) AS DaysToGo
>
> ===========================================================>|||BTW and FWIW, I added this exact example to the article.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)

Complex SSIS Lookup/Merge Join Using NK and Dates

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

Hi Brian. I would go ahead and join based on your NaturalKey, then use a secondary step such as a Conditional Split to filter based on the dates.

Jay|||Brian, I just came across the same problem. Any chance you can post the code example to accomplish this? thanks|||

There's a possible option here: http://blogs.conchango.com/jamiethomson/archive/2006/03/29/3294.aspx

Beware though, no data gets cached in memory.

-Jamie

Complex SSIS Lookup/Merge Join Using NK and Dates

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

Hi Brian. I would go ahead and join based on your NaturalKey, then use a secondary step such as a Conditional Split to filter based on the dates.

Jay|||Brian, I just came across the same problem. Any chance you can post the code example to accomplish this? thanks|||

There's a possible option here: http://blogs.conchango.com/jamiethomson/archive/2006/03/29/3294.aspx

Beware though, no data gets cached in memory.

-Jamie