You must Sign In to post a response.
  • Category: ASP.NET

    How to convert date Varchar Format to Date Format using sql query.

    In my project i need to search records by date.
    that is from DateTime to to DateTime.my i am create the DateTime in Varchar format.
    now i Search records using from and to DateTimeit is not show exact output.
    i thing my created date format is wrong and that is the reason.
    So i want to convert from date and to date from varchar datatype to DateTimeFormat.
    and also which format will be give exact output for me is very important.

    So please help me to how i am Convert already created data type Varchar to DateTime format
    or may i drop the column and recreate a new column with DateTime Format.

    i am having lot of stress with this module friends.

    Thanks with
    Paul.S
  • #766360
    Hi
    you can try this Query


    select CONVERT(varchar(20),date_time,101) from sample2

    OR

    select CONVERT(varchar(20),date_time,102) from sample2

    OR
    select CONVERT(varchar(20),date_time,103) from sample2

    OR
    select CONVERT(varchar(20),date_time,111) from sample2

    OR
    select CONVERT(varchar(20),date_time,110) from sample2

    OR

    select * from sample2 where CONVERT(varchar(20),date_time,101) between CONVERT(varchar(20),date_time,101) and CONVERT(varchar(20),date_time,101)

    select * from sample2 where CONVERT(varchar(20),date_time,102) between CONVERT(varchar(20),date_time,102) and CONVERT(varchar(20),date_time,102)


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766361
    Hi,
    Convert varchar to date:
    1. SELECT CONVERT(datetime, ColumnName, 110)
    FROM TableName
    WHERE CONVERT(datetime, ColumnName, 110) < '2015-08-22 00:00:00.000'
    and CONVERT(datetime, ColumnName, 110) > '2015-08-21 00:00:00.000'

    2. SELECT CAST(ColumnName AS datetime)
    FROM TableName
    WHERE CAST(ColumnName AS datetime) < '2015-08-22 00:00:00.000'
    and CAST(ColumnName AS datetime) > '2015-08-21 00:00:00.000'

    Please find meaning of different different datetime (Eg. 101,102 etc) formats over here:
    http://www.w3schools.com/sql/func_convert.asp

  • #766363
    you can use SQL CONVERT() function, that converts an expression of one data type to another.
    The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time

    CONVERT(VARCHAR(19),GETDATE())
    CONVERT(VARCHAR(10),GETDATE(),10)
    CONVERT(VARCHAR(10),GETDATE(),110)
    CONVERT(VARCHAR(11),GETDATE(),6)
    CONVERT(VARCHAR(11),GETDATE(),106)
    CONVERT(VARCHAR(24),GETDATE(),113)
    *SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

    you can also use CAST function, which will convert input data in specified date object
    see below link for more details
    https://msdn.microsoft.com/en-in/library/ms187928.aspx
    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766369
    Hi,

    You can alter the datatype of the column as datetime and then perform the whichever operations you want.
    Use this below query to alter the datatype of the column.
    <pre>
    alter table tab_nmae alter column column_name datetime
    </pre>

    I hope this could help you

  • #766370
    Heartful thanks for all of your valuable and quick reply friends. thanks a lot.

    Paul.S

  • #766371
    Hi,

    Rather than convert the column everytime my suggestion is Alter the datatype of the specified column and perform actions as you want.


    ALTER TABLE tablename
    ALTER COLUMN columnname DATETIME


    If you still want to convert with the fields while fetching records from database, I would suggest you to use CONVERT function to convert the appropriate format.


    SELECT CONVERT(DATETIME, @varchardate)


    Try something like above to achieve your goal, hope this will helpful to you....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments