February 7, 2012 at 9:19 am
hi
i have a column called ddate which is nvarchar (255) and contains values in the following format ''01 Sep 2011'.
I have created a new column called ddate2 where i want to convert the values from ddate into datetime format using an update statement.
I would like the data within ddate2 to be in the following format 'YYYYMMDD'
However when i run the following code
CONVERT (datetime, [ddate],112)
the following appears in ddate2 '2011-09-01'
what am i doing wrong?
thanks
February 7, 2012 at 9:33 am
What is the datatype of your new column? I suspect it is another nvarchar(255). Please use the proper datatypes. You have datetime information put it in a datetime column.
Look at this example. You will see that converting to datetime returns a datetime datatype but when you put that in an incorrect datatype you get exactly what you told it, which is your formatted string representation of datetime.
declare @NotADate nvarchar(255) = '01 Sep 2011'
select CAST(@NotADate as datetime)
declare @NotADate2 nvarchar(255)
select @NotADate2 = CONVERT(datetime, @NotADate, 112)
select @NotADate2, CONVERT(datetime, @NotADate, 112)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 10:10 am
based on your code i tried the following:
update [table1]
set [ddate2] = convert(datetime,CAST([ddate] as datetime),112)
but it now outputs '2011-09-01 00:00:00'
February 7, 2012 at 10:13 am
bharatgi (2/7/2012)
based on your code i tried the following:
update [table1]set [ddate2] = convert(datetime,CAST([ddate] as datetime),112)
but it now outputs '2011-09-01 00:00:00'
If your new destination is actually a datetime, which i certainly hope it is so you can avoid this kind of pain in the future, there is no need to format it.
set [ddate2] = CAST([ddate] as datetime)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 11:51 am
yes, the destination is set to datetime.
But the cast outputs in the following format '2011-08-09 00:00:00.000'
ideally i would like the dates without the seperators
February 7, 2012 at 12:21 pm
try this
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
GO
CREATE TABLE [dbo].[Table_1](
[ddate] [nvarchar](255) NULL,
[ddate2] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Table_1]([ddate])
SELECT N'01 Sep 2011'
UPDATE TABLE_1
set [ddate2] = CAST([ddate] as datetime)
select ddate2 from TABLE_1 --== std sql format
select CONVERT(nvarchar(8), ddate2, 112) from TABLE_1 --== display format
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 8, 2012 at 4:40 am
hi,
how would i get the field values to automatically show the display format rather than the sql standard format?
February 8, 2012 at 7:28 am
bharatgi (2/8/2012)
hi,how would i get the field values to automatically show the display format rather than the sql standard format?
Fields don't display stuff they simply store data. In this case the data that is being stored is a datetime value. Presentation really should be done in the front end but if you must use sql to make it look pretty you would need to use convert on your datetime column. http://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 12:32 pm
thanks, i have created a view with the extra columns to display in the way required
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply