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

Question

Post reply

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

forsqlserver

SSCoach

Points: 18902
More actions
August 5, 2011 at 3:38 am

#241796

There is a application A1 and it generates the data in table in dd-MM-YYYY format ,and application user want this date to be transferred to another database table by other application A2 in yyyy-MM-ddTHH:mm:ss.fff format.but yyyy-MM-ddTHH:mm:ss.fff has one T (See closely).
How to convert this 'dd-mm-yyyy' to 'yyyy-MM-ddTHH:mm:ss.fff' string format?
Thanks

Viewing 15 posts - 1 through 15 (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

Not sure what ".fff" is, but the "T" is ISO8601 -> code 126

SELECT CONVERT(VARCHAR(100),GETDATE(),126)

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 2

Its a greate help.

I have tried to save it in all time of time and date datatypes but no one is saving with T.

Please guide

Thanks

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

Gosh, NO! You shouldn't ever save formatted dates and times in the database. It messes up just about everything and it's a real pain when the user changes his/her mind. Do this type of formatting in the GUI and save all dates and times in the database as DATETIME. 😉

--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)

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Your first problem will be converting data in dd-mm-yyyy format to datetime. I think you will have to call "set language='British' " or "set dateformat = 'dmy' " before using convert.

Once you have the system successfully recognising dd-mm-yyyy format as representing dates (and not blowing a fuse on days after the 12th of each month) using convert with style 126 will work and deliver that T.

Of course holding dates in tables as character strings instead of as dates (or datetime, or datetime2) is usually a pretty crazy thing to do, as has already been pointed out.

Tom

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Tom Thanks,

Actually in the developement environment the forefront is installed and sql server instance is shared with other applications also.

So,The forefront admin requires there date in database table with T format and no data datatype is saving it with T.

Should I save it in varchar,nvarchar,char or nchar Please suggest.

Thanks

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

This has been answered above by skcadavre already? What don't you understand of it?

skcadavre (8/5/2011)
Not sure what ".fff" is, but the "T" is ISO8601 -> code 126
SELECT CONVERT(VARCHAR(100),GETDATE(),126)

If you really want to (you've already been told twice it's unwise to store dates as strings and I can only repeat it once more), then do as skcadavre suggests: convert the string into a datetime and then convert that datetime into another string, applying appropriate formatting on both conversions. The .fff part is optional and is left off by convert() when only .000 would be appended.

declare @sInput varchar(10);

set @sInput = '31-12-2010';

select convert(varchar(23), convert(datetime, @sInput, 103), 126);

The best thing would be for you to educate your forefront admin: tell him he can retrieve a value from a datetime column in (almost) any format he wants it. All he needs to do is add the appropriate formatting himself on the select clause of his queries. For example:

declare @dt datetime;

select @dt = getdate();

select convert(varchar(23), @dt, 126);

-----------------------

2011-08-09T08:30:05.623

(1 row(s) affected)

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?

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Hi Rozema,

Thanks

declare @dt datetime;

select @dt = getdate();

select convert(varchar(23), @dt, 126);

How can I store a value of a column in @dt?

as below query is giving error...

use enterpriseonly

go

declare @dt;

@dt=SELECT d1 FROM [EnterpriseOnly].[dbo].[datee];

Error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ';'.

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

use enterpriseonly

go

declare @dt;

SET @dt = (SELECT d1 FROM [EnterpriseOnly].[dbo].[datee]);

or

SELECT @dt = d1 FROM [EnterpriseOnly].[dbo].[datee];

This assumes table [datee] has only one row.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

forsqlserver SSCoach Points: 18902 More actions · Answer 9

Hi Chris,

Thanks,

I have not much idea about trigger.

Can I set the below scriptlet in a insert ,update trigger.

if data already exist then update should run otherwise insert should run.

use enterpriseonly

go

declare @dt datetime;

SET @dt = (SELECT d5 FROM [EnterpriseOnly].[dbo].[datee]);

update [datee] set d5=(select convert(varchar(50), @dt, 126)) where d5 like '2011-12-02%';

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

What action are you attempting to perform here?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

forsqlserver (8/18/2011)
Hi Chris,
Thanks,
I have not much idea about trigger.
Can I set the below scriptlet in a insert ,update trigger.
if data already exist then update should run otherwise insert should run.
use enterpriseonly
go
declare @dt datetime;
SET @dt = (SELECT d5 FROM [EnterpriseOnly].[dbo].[datee]);
update [datee] set d5=(select convert(varchar(50), @dt, 126)) where d5 like '2011-12-02%';

No, I think you're going the wrong way here... @dt is simply a variable to hold a single value. My example was set up to demonstrate the string-to-date-to-string conversion on this single value. You can have SQL server do the same conversion on a set of such values by applying the conversion not on a variable, but on a column in the rows you've selected for the update statement. You don't need the variable at all.

For example, the following T-SQL updates column [d5] to the new format on all rows in table [datee] for which the column [d5] currently holds a string starting with '2011-12-02':

update [datee]

set d5 = convert(varchar(23), convert(datetime, d5, 103), 126)

where d5 like '2011-12-02%';

You should not even be thinking of creating triggers with the level of T-SQL knowledge you show (no offence intended). They are not the way to tackle this problem and will only make your life miserable ;-). As was said before: the proper solution to keep yourself (and your client/company) out of problems is to change the definition of the table. Change the type of column [d5] from it's current varchar(50) (or something similar) into date, datetime or datetime2. Which of these to select depends on the requirements for the values in this column. Suggested reading material on the topic of dates: msdn & books online.

edit: Actually, that won't work: the date string you've selected to update is not in the proper format. We were taking dates formatted 'dd-mm-yyyy' for our input weren't we? Are you changing the requirements already or is that a typo? It should be "like '02-12-2011%'".

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?

forsqlserver SSCoach Points: 18902 More actions · Answer 12

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?

Thanks

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

Then give us the DDL for the table in HRMS (the source of the information) and we can give you the select statement giving the forefront admin the dates in the format he wants it in.

In other words: fix the problem at it's source, not where it hurts.

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?

forsqlserver SSCoach Points: 18902 More actions · Answer 14

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.

Thanks