dateformat

[suriyakaqnnan]

how to convert the nvarchar datatype to datetime

[Sandeep Mewara]

You can use CONVERT: Here you go: CAST and CONVERT[^] How to format a Date or DateTime in SQL Server[^] Example:

SELECT CONVERT(VARCHAR, columnName, 103) AS MyDate From MyTable;

Sandeep Mewara Microsoft ASP.NET MVP [My latest Article]: Server side Delimiters in ASP.NET[^]

[suriyakaqnnan]

thank you friend but i want to convert the varchar dataype to datetime format (ie.)if i enter 22.3.2004 in date of birth text box it should to stored on the database as 22/3/2004 how it possible please reply me and also if the user enter like 22.3.2004 error msg has to display pls reply both

[Sandeep Mewara]

suriyakaqnnan wrote:

i want to convert the varchar dataype to datetime format

I replied/shared the exact same question. :doh:

suriyakaqnnan wrote:

f the user enter like 22.3.2004 error msg has to display

Why? If needed, put your logic on frontend or UI layer. Validate and then pass on to DB. Try!

Sandeep Mewara Microsoft ASP.NET MVP [My latest Article]: Server side Delimiters in ASP.NET[^]

[Albarhami]

You can use Convert.to... in C# or CAST('09/25/2004'AS DATETIME)... in SQL Server

[Albarhami]

For more details about all datatype conversion in C# have a look here: http://www.c-sharpcorner.com/uploadfile/puranindia/type-conversions-in-C-Sharp/[^]

[KiranKumar Roy]

use below SQL function to convert DD/MM/YYYY string to Datetime.. Make sure every time u pass "DD/MM/YYYY" format only.

CREATE FUNCTION ChangeDateFormat
(
@STRING NVARCHAR(40),
@DELIMITER NVARCHAR(40)
)
RETURNS DateTime
AS
BEGIN
DECLARE @MM as varchar(5)
DECLARE @DD as varchar(5)
DECLARE @YY as varchar(5)
DECLARE @POS INT
DECLARE @NEXTPOS INT
DECLARE @INT INT
DECLARE @TEMP as varchar(50)
SET @STRING = @STRING + @DELIMITER
SET @POS = charindex(@Delimiter,@String)
SET @INT = 1

WHILE (@POS <> 0)
BEGIN
	if @INT = 1 
		Begin
			SET @DD = substring(@String,1,@Pos - 1)
		End
	if @INT = 2 
		Begin
			SET  @MM = substring(@String,1,@Pos - 1)	
		End
	if @INT = 3 
		Begin
			SET @YY = substring(@String,1,@Pos - 1)	
		End	
	
	SET @STRING = substring(@String,@pos+1,len(@String))
	SET @POS = charindex(@Delimiter,@String)
	SET @INT = @int + 1
END 
SET @TEMP = @MM+'/'+ @DD + '/' + @YY

Return  convert(datetime, @TEMP)

END

KiranKumar Roy