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

    How to bring a record related to month and day

    hi all,
    i have completed the project but still little bit pending
    i have birthday images so ihave one column dateof birth with datatype date
    so when in ui i am inserting dob as 2015-07-29 ie sysdate so image is coming but actually that dateof birth is 1986-07-29 how to retrieve image i have entered like1986-07-29
    my code is

    string sqlconstr = "Data Source=HOME;Initial Catalog=Registration;Integrated Security=True";
    SqlConnection con = new SqlConnection(sqlconstr);
    SqlDataAdapter sqlda = new SqlDataAdapter("select * from imagepath where CurrentDate is NULL union ALL select * from imagepath where convert(varchar(20), CurrentDate, 101) = convert(varchar(20), getdate(), 101)", con);
    dt = new DataTable();
    sqlda.Fill(dt);
    TotalRecords = dt.Rows.Count;
    Image1.ImageUrl = "~/Upload/" + dt.Rows[0]["ImageName"].ToString();

    //Response.Redirect("uploadsite.aspx");
    SqlDataAdapter da1 = new SqlDataAdapter("select ImageName from imagepath where convert(varchar(20), CurrentDate, 101) = convert(varchar(20), getdate(), 101)", sqlconstr);
    DataSet ds1 = new DataSet();

    da1.Fill(ds1, "imagepath");
  • #761035
    Hi
    velidram

    what is your issue?

    If you need convert dataformat and fetch data means

    You can use this code


    Select GetDate()
    Select CONVERT(varchar(12),GetDate(),101)
    Select CONVERT(varchar(12),GetDate(),102)
    Select CONVERT(varchar(12),GetDate(),103)
    Select CONVERT(varchar(12),GetDate(),104)
    Select CONVERT(varchar(12),GetDate(),105)
    Select CONVERT(varchar(12),GetDate(),106)
    Select CONVERT(varchar(12),GetDate(),107)

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

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

  • #761048
    Basically if culture of your computer is English (United States) then the SQL server stored dates in the form of MM/dd/yyyy, here you just need to pass the date value in MM/dd/yyyy format, see below query

    select * from imagepath where CurrentDate = '07/29/1987'

    hope it helps

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

  • #761054
    Hello,

    You can refer the below query :

    SELECT * FROM tablename WHERE MONTH(columnname) = 7 AND DAY(columnname) = 29

    columnname = BirthDate column

    Your columnname datatype should be datetime or date to get exact output.

    Hope this will work for you.
    Mark the answer if it helped you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761147
    Hai Velidram,
    The problem is that you have written the criteria which is not matching for the old birthdates.

    SqlDataAdapter sqlda = new SqlDataAdapter("select * from imagepath where CurrentDate is NULL union ALL select * from imagepath where convert(varchar(20), CurrentDate, 101) = convert(varchar(20), getdate(), 101)", con);

    If you want that it should work for all the birth dates, then you can replace the code as below:

    SqlDataAdapter sqlda = new SqlDataAdapter("select * from imagepath where CurrentDate is NULL union ALL select * from imagepath where convert(varchar(20), CurrentDate, 101) <= convert(varchar(20), getdate(), 101)", con);

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments