SQL Server - convert string to yyyy/mm/dd using sql 2000
Asked By Itu Kosh
09-Feb-10 12:33 PM
Hi,
I'm trying to insert a string in SQL that look like this '24/01/2010' and I'm get an error that I cannot convert string to datetime. I'm trying to convert '24/01/2010' to '2010/01/24' (yyyy/mm/dd) and I need help as to how to go about it.
Your assistance would be greatly appreciated.
ItuKosh
Jonathan VH replied to Itu Kosh
CONVERT(datetime,'24/01/2010',103)
Web Star replied to Itu Kosh
u can use convert or cast function in sql server for convert datatype
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
F Cali replied to Itu Kosh
This will involve 2 CONVERT statements. The first one will convert the DD/MM/YYYY date string into a DATETIME data type. The second one will convert the DATETIM data type to a string/varchar with your specified format. Here's how to do it:
SELECT CONVERT(VARCHAR(10), CONVERT(datetime,'24/01/2010',103), 111)
For other date formats, you can refer to the following link:
http://www.sql-server-helper.com/tips/date-formats.aspx
Regards,
http://www.sql-server-helper.com/error-messages/msg-8115-numeric-to-varchar.aspx
Itu Kosh replied to F Cali
Thank you this actually worked...
DECLARE
@Date VARCHAR(12)
SET @Date= '22/01/2010'
SET
@Date= SUBSTRING (@Date,7,4) + '/' + SUBSTRING(@Date,4,2) + '/' + SUBSTRING(@Date,1,2)
SELECT
@Date
ItuKosh
F Cali replied to Itu Kosh
Yes, you can also do it that way, using string manipulation. Here's another way to do it using string manipulation using a combination of the LEFT, RIGHT and SUBSTRING string functions:
DECLARE @Date VARCHAR(10)
SET @Date= '22/01/2010'
SET @Date= RIGHT(@Date, 4) + '/' + SUBSTRING(@Date,4,2) + '/' + LEFT(@Date,2)
SELECT @Date
Regards,
http://www.sql-server-helper.com/error-messages/msg-8134.aspx
Jonathan VH replied to Itu Kosh
Your first post stated that you received an error that you "cannot convert string to datetime" when inserting. Your "solution" converts the string to another string, which would then require another (implicit) conversion to update a datetime column. If the column is datetime, as you wrote, all you need is the formula from my reply, e.g.:
INSERT dbo.YourTable(YourDatetimeColumn) VALUES(CONVERT(datetime,@Date,103));
Fill(ds, “Products”) DataGrid1.DataSource = ds.Tables(”Products”) What is the maximum length of a varchar in SQL Server? Answer1 VARCHAR[(n)] Null-terminated Unicode character string of length n, with a maximum of 255 characters The presentation logic is done with .aspx extention. How do you define an integer in SQL Server? We define integer in Sql server as var_name int How do you separate business logic while creating an ASP.NET application
how to declare date in a table in sql 2005 i m using sql 2005, how to declare a date in ddmmyyy format in a table i m using datetime datatype but it is acepting date in yyyymmdd format and also time i want that the table must accept only date not time HI try this - -declare our date variables DECLARE @LowerBoundDate DATETIME DECLARE @UpperBoundDate DATETIME DECLARE @IteratingDate DATETIME - -set the initial dates SET 1 / 1 / 1980' SET @UpperBoundDate = '1 / 1 / 2040' - -create our temporary table DECLARE @TempDateDimension TABLE ( Date DATETIME , [ Year ] INT , [ Month ] INT , [ Day ] INT ) - -set the iterating date to the lowerbound date
birthdate i have given the datatype of datetime, i want that when the user enter date in any format it should be adjusted as dd / mm / yyyy suppose he gives data should get change to dd / mm / yyyy is it possible please reply DateTime columns in SQL Server are stored in a binary format. If you want your dates displayed a certain way out, you cannot change the way it is stored. Do you want to store the date in that particular format in database? If you want it for display purpose only that 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format. – Microsoft SQL Server T-SQL date and datetime formats – Date time formats – mssql datetime – MSSQL getdate returns current system date and
date time format hi, I want to retrieve the date from sql server to dot net, but i want that in dd / mm / yyyy format.actually i have taken the date time datatype in sql server.but i want only dd / mm / yyyy.in front end when i retrieve from database pls help me how can i restrict time from date time, pls help me as early as possible SELECT TOP 1 ID, Start_Date, 3 > CONVERT
scale auditing and accountability. This is a technique I have used with great success in SQL Server 2005 though. The next is 3rd party software like Idera's compliance manager ( http: / / www.idera.com / Products / SQL-Server / SQL-compliance-manager / ). I have done evaluations on them and found that for most purposes I you can use third party log reader software to see all changes. For instance Apex SQL Log http: / / www.apexsql.com / sql_tools_log.aspx . This software is extremely useful in certain recovery someone deliberately trying to hide changes. The bottom line is that if you are using SQL Server 2008 then Change Data Capture is probably the way to go, and if you are