Convert NVARCHAR to DATETIME in SQL Server 2008

Question

In my table  LoginDate   2013-08-29 13 55 48     The loginDate column s datatype is nvarchar 150     I want to convert the logindate column into date time format using SQL command    Expected result    LoginDate   29-08-2013 13 55 48

User · Answer

what about this  --   Convert NVARCHAR to DATETIME DECLARE  date DATETIME    SELECT convert DATETIME   2013-08-29 13 55 48   120   --   Convert DATETIME to custom NVARCHAR FORMAT SELECT  RIGHT  00   CAST DAY  date  AS NVARCHAR  2     -    RIGHT  00   CAST MONTH  date  AS NVARCHAR  2     -     CAST YEAR  date  AS NVARCHAR           CAST CONVERT TIME  date  AS NVARCHAR    result   29-08-2013 13 55 48 0000000

User · Answer

As your data is nvarchar there is no guarantee it will convert to datetime  as it may hold invalid date time information  - so a way to handle this is to use ISDATE which I would use within a cross apply   Cross apply results are reusable hence making is easier for the output formats                          YOUR DT               SQL2008    ----------------------------- ---------------------            2013-08-29 13 55 48   29-08-2013 13 55 48        2013-08-29 13 55 48 blah                 null      2013-08-29 13 55 48 rubbish                 null     SELECT    Your Dt    convert varchar  ca1 dt converted  105          convert varchar  ca1 dt converted  8  AS sql2008 FROM your table CROSS apply   SELECT CASE WHEN isdate  Your Dt     1                         THEN convert datetime  Your Dt                           ELSE NULL                      END               AS ca1  dt converted      Notes     You could also introduce left  Your Dt  19  to only get a string like  2013-08-29 13 55 48  from  2013-08-29 13 55 48 rubbish   For that specific output I think you will need 2 sql 2008 date styles  105  amp  8  sql2012 added for comparison  declare  your dt as datetime2 set  your dt    2013-08-29 13 55 48   select   FORMAT  your dt   dd-MM-yyyy H m s   as sql2012   convert varchar   your dt  105          convert varchar   your dt  8  as sql2008                SQL2012               SQL2008    --------------------- ---------------------    29-08-2013 13 55 48   29-08-2013 13 55 48

User · Answer

alter table your table alter column LoginDate datetime    SQLFiddle demo

User · Answer

SELECT CONVERT NVARCHAR  LoginDate  105      CONVERT NVARCHAR  LoginDate  108  AS LoginDate FROM YourTable  Output ------------------- 29-08-2013 13 55 48

User · Answer

DECLARE  chr nvarchar 50     SELECT CONVERT nvarchar 50   GETDATE    103    SELECT  chr chars  CONVERT date   chr  103  date again

User · Answer

What you exactly wan t to do    To change Datatype of column you can simple use alter command as      ALTER TABLE table name ALTER COLUMN LoginDate DateTime    But remember there should valid Date only in this column however data-type is nvarchar   If you wan t to convert data type while fetching data then you can use CONVERT function as       CONVERT data type length  expression style    eg       SELECT CONVERT DateTime  loginDate  6    This will return 29 AUG 13  For details about CONVERT function you can visit                http   www w3schools com sql func convert asp   Remember  Always use DataTime data type for DateTime column   Thank You

[sql] Convert NVARCHAR to DATETIME in SQL Server 2008

Examples related to sql

Examples related to datetime

Examples related to sql-server-2008-r2

Examples related to nvarchar