October 13, 2011 at 9:36 am
hi
how can i use date conversation function to convert date in 'CCYY-MM-DDThh:mm:dd' format in sql
October 13, 2011 at 9:41 am
Try BOL
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
October 13, 2011 at 9:43 am
that i know,i want the exact format
October 13, 2011 at 9:53 am
daveriya (10/13/2011)
that i know,i want the exact format
Look up CONVERT in BOL. It will tell you exactly what you need to know.
Here is the link: http://msdn.microsoft.com/en-us/library/ms187928.aspx
October 13, 2011 at 10:01 am
select convert(datetime, '1967-10-13T17:55:12', 126)
Do you want me to chew your food for you too? 😉
Note the comments on BOL though: no spaces!, i.e. one minute past 1 on January first that year must be denoted as '1967-01-01T01:01:01', NOT '1967-1-1T1:1:1'.
October 13, 2011 at 10:24 am
i am getting this error : [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string.
i place 'set @Account_date = convert(datetime,'yyyy-mm-ddThh:mm:ss.mmm',126)'
October 13, 2011 at 10:54 am
daveriya (10/13/2011)
i am getting this error : [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string.i place 'set @Account_date = convert(datetime,'yyyy-mm-ddThh:mm:ss.mmm',126)'
What date are you trying to put in @Account_date? If you are trying to put todays date in this variable, all you need is this:
set @Account_date = getdate();
October 13, 2011 at 11:07 am
daveriya (10/13/2011)
i am getting this error : [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string.i place 'set @Account_date = convert(datetime,'yyyy-mm-ddThh:mm:ss.mmm',126)'
When was yyyy-mm-ddThh:mm:ss.mmm?
That's a format, not a date and time.
If someone asks who you are, do you introduce yourself as "Firstname Lastname", or perhaps "Lastname comma Firstname"? No. Those are formats, not values. (Well, unless your parents were psychotic, I guess.)
Same for this.
The "Convert()" function needs you to provide a value to it, not a string format.
For example:
SELECT
convert(datetime, '11/5/12', 2),
convert(datetime, '11/5/12', 3),
convert(datetime, '11/5/12', 1);
The first one, because the format chosen is 2, will be 12 May 2011 (assuming default 2-digit-year range on the server). The second, style 3, will be 11 May 2012. And the third will be 5 Nov 2012 (style 1).
But the second parameter for the function has to be a value, not a format.
(Edit for layout.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 11:10 am
IS THIS correct
set @Account_date = convert(datetime,@Account_date,126)
October 13, 2011 at 11:15 am
daveriya (10/13/2011)
IS THIS correctset @Account_date = convert(datetime,@Account_date,126)
That probably won't do anything useful for you.
Storing the data in a particular format is only useful if you're converting to a string datatype. What datatype is @Account_date?
If you look at how "convert" works, what you're doing is telling it that the data is a string in the format you want, and that you want it converted to a datetime datatype.
Datetime is stored as an integer for the days since "day zero", and a decimal portion for the 300ths of a second since time zero on that date.
So, if you have the default settings on your server and 1 Jan 1900 is "day zero", then "convert(datetime, 0)" will return midnight at the beginning of 1 Jan 1900. "convert(datetime, 1)" will give you one day later, which is 2 Jan 1900. And so on.
So, what kind of data does @Account_date have in it? How is it declared?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 11:20 am
account_date is declared as a datetime,but i want format CCYY-MM-DDThh:mm:ss
how to do it.coz i wan this parameter in cognos in this format
October 13, 2011 at 11:23 am
Generally, you're better off doing that kind of thing in the presentation layer, not in the query or the database.
That allows different users/applications/services to consume the data the way they like it best.
However, if you need to do that in a query:
SELECT convert(char(23), @Account_date, 126);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 11:32 am
I'm sure you already know this, but given the way this conversation seems to be going, I'm going to notate Gus's solution anyway.
Note: For Gus's solution to work, you have to have already set @Account_Date to equal some sort of date value. You can't just use that without declaring and setting the @Account_Date variable previous to his SELECT statement.
October 13, 2011 at 6:56 pm
daveriya (10/13/2011)
i am getting this error : [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string.i place 'set @Account_date = convert(datetime,'yyyy-mm-ddThh:mm:ss.mmm',126)'
The datetime format you've selected is one of those so called "universal date/time" formats and requires no conversion for use in SQL Server. Example follows...
DECLARE @Account_Date DATETIME
;
SELECT @Account_Date = '1967-10-13T17:55:12'
;
SELECt @Account_Date
;
--Jeff Moden
October 14, 2011 at 4:41 am
The opening post seemed to ask for a string to datetime conversion. However as was clarified only recently, poster was actually looking for a conversion from datetime into a formatted character string. Which was then correctly answered by GSquared.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply