How to convert this 'dd-mm-yyyy' to 'yyyy-MM-ddTHH:mm:ss.fff' string format?

Question

How to convert this 'dd-mm-yyyy' to 'yyyy-MM-ddTHH:mm:ss.fff' string format?

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

forsqlserver (8/19/2011)
Thanx,
Here HRMS persons are not giving rights on there table they only provides data...and the forefront database save the date columns in format of varchar.
Can it possible the date format should change At the time of insertion/updation from hrms database to forefront database on the forefront database table's column,as I have written earlier.

Trigger?

If that doesn't help, please read this[/url] and supply sample data along with expected output/result.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

forsqlserver (8/19/2011)
...
Can it possible the date format should change At the time of insertion/updation from hrms database to forefront database on the forefront database table's column,as I have written earlier.

Yes, that is possible using triggers. But triggers are hard to write correctly and when done wrong, they can easily mess up the functionality of your existing HRMS application. If your admins are not allowing you to see even the DDL of the tables, they will NEVER allow you to put a trigger onto their tables.

You just need to make sure you get the dates in the right format before or while you feed them into the forefront database. This is what I was trying to accomplish for you.

Give us something to work with and we can help you, as this is not a complicated problem at all. However, you're still letting us guess at what you've got. For example, how is the data transferred from the hrms database into this forefront database? Do you export into a file and then import from there or are the both databases in the same server or do you have a linked server connection between the both, or anything else?

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

forsqlserver (8/18/2011)
Thanks Rozema and chris: My requirement is:
I have two databases one is of HRMS application and another for forefront application.Now some table data comes from HRMS database to forefront database in which date column also comes.
Forefront accept date in the format of 2011-12-02T00:00:00 and forefront admin wants, whatever date is coming from HRMS database table a trigger should be configured on insert and update of that table and convert the format to 2011-12-02T00:00:00
Can u help me in creating trigger for this?

Gosh... I'll say it again... storing formatted dates in SQL Server is "death by SQL". Store the dates as a DATETIME datatype and if you really must have formatted dates for something else, forget about triggers and create a View to do the conversions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

forsqlserver SSCoach Points: 18902 More actions · Answer 4

Hi all,

Some how I have created this below trigger

create TRIGGER trgAfterInsert6 ON [dbo].[Employee_Test_datee]

FOR INSERT

AS

declare @emppdate varchar(50);

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @prim int;

declare @empid_date varchar(50);

select @emppdate=i.emp_date from inserted i;

select @prim=i.prim from inserted i;

set @empid_date=(SELECT (@emppdate+'T00:00:00.000'));

select @empid_date;

update Employee_Test_datee set emp_date=@empid_date where ????? ;

GO

Table Design:

CREATE TABLE Employee_Test_datee

(

Emp_date varchar(30),

Emp_name Varchar(100),

Emp_Sal Decimal (10,2),

prim int identity

)

This trigger is created for changing the emp_date value at the time of insertion.

I have a problem is that I am not able to set the where condition.I want that only the current inserted value should be edited and saved again on the same place.

Thanks

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

As has been said to you repeatedly, storing dates as anything other than DATE or DATETIME is a recipe for disaster later on.

I've not tested this code, so please test thoroughly.

CREATE TRIGGER trgafterinsert6

ON [dbo].[Employee_Test_datee]

FOR INSERT

AS

DECLARE @emppdate VARCHAR(50);

DECLARE @prim INT;

SELECT @emppdate = i.emp_date, @prim = i.prim

FROM inserted i;

UPDATE employee_test_datee

SET emp_date = @emppdate + 'T00:00:00.000'

WHERE prim = @prim;

GO

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

forsqlserver SSCoach Points: 18902 More actions · Answer 6

The problem is of forefront application it only understood the date in format of

yyyy-MM-ddTHH:mm:ss.fff (For example: 2009-11-06T07:00:00.000)

Thanks

TomThomson SSC Guru Points: 104773 More actions · Answer 7

forsqlserver (8/23/2011)
Hi all,
Some how I have created this below trigger

It's better to write a trigger that doesn't declare local varaibles, just in case at some time in the future some application shows up that want's to insert more than one row with a single statement. It also gives you much shorter text for the trigger and probably executes faster even for single row inserts.

Try something like this:

create TRIGGER trgAfterInsert6 ON [dbo].[Employee_Test_datee]

FOR INSERT

AS

update Employee_Test_datee set emp_date=emp_date+'T00:00:00.000'

where prim in (select prim from inserted)

GO

You may also want to cope with a new application that inserts dates already in the right format, in which case you could add " and Employee_Test_datee not like '%T%' " to the where condition.

I still think it's pretty awful keeping dates in string format instead of keeping them as dates and converting in the sql statement that reads them - you are just storing up more trouble for the future - but if you are stuck with an app that insists on reading data from the DB in its own stupid manner I guess a trigger like the above is the only way out.

Tom

forsqlserver SSCoach Points: 18902 More actions · Answer 8

forsqlserver

SSCoach

Points: 18902

August 23, 2011 at 5:31 am

#1371888

Tom u r genius everyone knows...

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 9

The same I have to done for update query can I use single trigger for both purpose...

Thanks

TomThomson SSC Guru Points: 104773 More actions · Answer 10

forsqlserver (8/23/2011)
The same I have to done for update query can I use single trigger for both purpose...

Yes, same trigger will work for both update and insert provided you include " and ....." in the where condition as I suggested you might want to.

Tom

forsqlserver SSCoach Points: 18902 More actions · Answer 11

Tom I have a confusion here,

Here we have FOR INSERT in trigger statement that means it will run on insert command only.

How will it run for update?

Thanks

TomThomson SSC Guru Points: 104773 More actions · Answer 12

forsqlserver (8/23/2011)
Tom I have a confusion here,
Here we have FOR INSERT in trigger statement that means it will run on insert command only.
How will it run for update?

Instead of

create TRIGGER trgAfterInsert6 ON [dbo].[Employee_Test_datee]

FOR INSERT

you would need to have

create TRIGGER trgAfterInsert6 ON [dbo].[Employee_Test_datee]

FOR INSERT, UPDATE

Tom

forsqlserver SSCoach Points: 18902 More actions · Answer 13

Tom please help one more thing

Use HRMS

Go

Create TRIGGER SteagingAfterInserttest ON [dbo].[staging]

FOR INSERT,UPDATE

AS

Update Staging set Startdate=Startdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

Update Staging set Terminationdate=Terminationdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

Update Staging set OfficiatingStartDate=OfficiatingStartDate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

GO

Here these 3 dates will come randomly so I want to apply if condition so that only that update query can run.

Please help..

Thanks

TomThomson SSC Guru Points: 104773 More actions · Answer 14

forsqlserver (8/24/2011)
Tom please help one more thing
Use HRMS
Go
Create TRIGGER SteagingAfterInserttest ON [dbo].[staging]
FOR INSERT,UPDATE
AS
Update Staging set Startdate=Startdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);
Update Staging set Terminationdate=Terminationdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);
Update Staging set OfficiatingStartDate=OfficiatingStartDate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);
GO
Here these 3 dates will come randomly so I want to apply if condition so that only that update query can run.
Please help..

I'm not sure exactly what you mean here, but if the idea is to update only columns that need updating the three lines could just change to be

Update Staging set Startdate=Startdate+'T00:00:00.000'

where EmpId in (Select EmpId from inserted) and Startdate not like '%T%' and

len(Startdate) > 4;

Update Staging set Terminationdate=Terminationdate+'T00:00:00.000'

where EmpId in (Select EmpId from inserted) and Terminationdate not like '%T%' and

len(Terminationdate) > 4;

Update Staging set OfficiatingStartDate=OfficiatingStartDate+'T00:00:00.000'

where EmpId in (Select EmpId from inserted) and OfficiatingStartdate not like '%T%' and

len(OfficiatingStartdate) > 4;

This will work for multi-row inserts and updates, and for single row inserts updates, and for inserts/updates that supply any 1 of the three dates or any 2 or all three (or in fact none at all - it does nothing in that case). What it does is (i) avoid touching dates that aren't long enough to be real dates or are null and (ii) avoid adding the T00:00:00.000 to something that already has it.

Tom

forsqlserver SSCoach Points: 18902 More actions · Answer 15

Thanks TOM

But I am asking about the if else condition:

Suppose in one row at one time one startdate is coming but other termination date and officiatingstartdate is coming as null.then the update query should run only for startdate.

That can be implement using if else

I have implemented it as below but it is working only for Insert not for update:

Create TRIGGER S3 ON [dbo].[staging]

FOR INSERT,Update

AS

IF ((select Startdate from inserted)=NULL)

PRINT 'StartDate is NULL';

ELSE

BEGIN

Update Staging set Startdate=Startdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

End

IF ((select Terminationdate from inserted)=NULL)

PRINT 'Terminationdate is NULL';

ELSE

BEGIN

Update Staging set Terminationdate=Terminationdate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

End

IF ((select OfficiatingStartDate from inserted)=NULL)

PRINT 'OfficiatingStartDate is NULL';

ELSE

BEGIN

Update Staging set OfficiatingStartDate=OfficiatingStartDate+'T00:00:00.000' where EmpId in (Select EmpId from inserted);

End

GO

=========================

Error coming in update is:

Msg 8152, Level 16, State 14, Procedure S3, Line 20

String or binary data would be truncated.

The statement has been terminated.

================================Edited==============

I am getting this error if I am updating a value that is is stored in the table in 02-02-2001T00:00:00.000 format otherwise its fine working.

Thanks