Splitt DateTime and more - 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 2008 Forums

Transact-SQL (2008)

Splitt DateTime and more

Author

Topic

skalldem
Starting Member

2 Posts

Posted - 2011-08-31 : 09:28:50

Hi together,

at First: Sry my english issent so good, but i hope you understand me...

My MSSQL knowledge is not so good, but i want to create a sript that split DateTime to Date and Time.

In my first Database i have a Field with DateTime format. I create a T-SQL Sctipt that copy all importent information von dbOld to a New DB.

quote:

/****** Skript für SelectTopNRows-Befehl aus SSMS ******/
/**** leeren der Aktuellen Tabelle
Truncate table [NeueDB].[dbo].[InCall] ****/

/**** Einfügen der nicht angenommen anrufe****/
INSERT INTO NeueDB.dbo.InCall(ID, Wochentag, Anrufdauer, DW, Typ, Datum, Abgelehnt, Uhrzeit)
SELECT [lPKId]
,[sGiorno_c]
,[dDurata]
,[sDerivato]
,[sTipo]
,[dGatewayDateTime]
,[sEsitoChiamata_c]
,[dGatewayDateTime]
FROM [AlteDB].[dbo].[Chiamate]
where [AlteDB].[dbo].[Chiamate].[dGatewayDateTime] LIKE '%2011%' and sTipo = 'I' and sEsitoChiamata_c = '0';

/**** Einfügen der angenommen anrufe ****/
INSERT INTO NeueDB.dbo.InCall(ID, Wochentag, Anrufdauer, DW, Typ, Angenommen, Datum, Uhrzeit)
SELECT [lPKId]
,[sGiorno_c]
,[dDurata]
,[sDerivato]
,[sTipo]
,[sEsitoChiamata_c]
,[dGatewayDateTime]
,[dGatewayDateTime]
FROM [AlteDB].[dbo].[Chiamate]
where [AlteDB].[dbo].[Chiamate].[dGatewayDateTime] LIKE '%2011%' and sTipo = 'I' and sEsitoChiamata_c = '1';

sTipe = Incomming Calls -> I; Outgoint Calls -> O
sEsitoChiamata_c = 1 OK; 0 = Busy/NR

dGateway is the column in DateTime format.

I need a script that filter all incomming Calls out off the Office Time. Also not between 9:00 and 17.30 o'clock.

But i dont know how i can make it with DateTime...

An other idee was that:


SELECT [Datum]
    ,[DW]
    ,[Wochentag]
  FROM [NeueDB].[dbo].[InCall]
  where DATEPART (HOUR, Datum) not between '9' and '17' and DATEPART (YEAR, Datum) like '%2011%' and Abgelehnt = '0'
  Order by 'Wochentag';

But with "Datepart" i can get only on information (HH, YY, MIN usw..) not two (HH and MIN).

I Hope someone can help me...

BG
Skalldem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 10:12:58

see logic here

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-31 : 10:21:24

[code]
SELECT [Datum]
,[DW]
,[Wochentag]
FROM [NeueDB].[dbo].[InCall]
WHERE CONVERT (TIME, Datum) BETWEEN '09:00' AND '17:00'
AND DATEPART (YEAR, Datum) = 2011
AND Abgelehnt = '0'
ORDER BY [Wochentag];
[/code]

KH
[spoiler]Time is always against us[/spoiler]

skalldem
Starting Member

2 Posts

Posted - 2011-09-01 : 02:19:28

Thank your! That works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources