Android Dvlpr February 2016

fetch date from datetime column in sql

I have a Product table.I want fetch date from datetime column. I wrote this query.

Query code is here

SELECT Date = CONVERT(DATE, Date) FROM Product

Query is running sql server management studio and returning the correct date but when called from C# the query runs and returns a result but it returns datetime rather than just the date.

So for example if the query:

SELECT Date = CONVERT(DATE, Date) FROM Product

returns 09-02-16 in management studio

the C# code

SqlDataAdapter adr;
        adr = new SqlDataAdapter("SELECT Date = CONVERT(DATE, Date) FROM Product", conn);

returns 09.02.2016 00:00:00

What I would like the C# code to return is: 09-02-16

Please help.

Answers


devopsEMK February 2016

Try with this SELECT CONVERT(date,[Date]) as date FROM Product


Alexei February 2016

Date is a keyword in SQL, so I would avoid using it:

var adr = new SqlDataAdapter("SELECT CAST([Date] AS DATE) AS OnlyDate FROM Product", conn);


Rahul February 2016

Your query is certainly wrong. either you want to do

SELECT CONVERT([DATE], 102) FROM Product

(OR) declare a variable and store the data there like

DECLARE @Date DATETIME;
SELECT @Date = TOP 1 CONVERT([DATE], 102) FROM Product

Assuming you have a column named DATE and you will have to use a proper format string unless you are passing format string as parameter. Though your query is bit unclear.


Fabian Bigler February 2016

Based on your comment:

I don't have an error. Query code running result: 2015-11-23. But c# code is running result : 23.11.2015 00:00:00

I assume that your query is already working properly.

However, the format of the date is different!

So all you need to do is to convert the DateTime in your wished format.

Note: Per default the culture of your thread is based on the settings of your computer (In your case, it's dd.MM.yyyy). The result of the query on SQL Management Studio is depending on the column you are selecting (In your case: yyyy-MM-dd).


Kamran Farzami February 2016

based on you comment you may want to try:

SqlDataAdapter adr;
        adr = new SqlDataAdapter("SELECT [Date] = convert(VARCHAR,[Date], 5) FROM Product", conn); 

The convert(datetime,[date], 5) will ensure you are returning the result as DD-MM-YY


Igor February 2016

c# code is running result : 23.11.2015 00:00:00

How are you seeing this and where? In the debugger in VS? On the screen?

It is probably just formatting based on your culture (the one set on your PC). You are probably not understanding the difference between viewing the string representation in C# of a DateTime object, which can be formatted based on your locale, and the default ISO formatting in Sql Server.

Also there is no such thing in c# as just a date object, its always a date with time (DateTime) although the time component will then have 0 values for the time aspects of the object.

In short it is probably working the way it should.


Android Dvlpr February 2016

True code is here :

 SqlDataAdapter adr; 
    adr = new SqlDataAdapter("SELECT LEFT(CONVERT(VARCHAR, Date, 120), 10) from Product", conn);

Post Status

Asked in February 2016
Viewed 1,796 times
Voted 12
Answered 7 times

Search




Leave an answer