Date formats

1

hi all
i have a date format as '8/17/2020 4:28:46 PM'
want date as 17-aug-2020
please help
If I use format() function i get varchar not date

2

hi hope this helps

declare @var varchar(100) = '8/17/2020 4:28:46 PM'

select CONVERT(nvarchar(30),cast(@var as date), 6)

image

1 Like
3

thanks
would I be able to filter with this?
I mean
.. where convert(nvarchar(30),cast(@var as date),6)>='01-apr-2021'
like this?
Originally my value i posted is a date...so?

4

It's better to filter date using date

Example

Date123 > cast(01-april-2021 as date )

5

Is this in a variable or column? SQL Server does not store dates in a string format - not sure what you are trying to resolve.

6

Blockquote
this is a value stored in a datetime column sir

7

Okay - you have a datetime column and you want it to display in a different format? Where do you want it to be displayed differently?

Again - that column is not stored with a format, it is stored as a numeric value.

8

sir
Please elaborate..
Actually we crated a table from a csv file. Data is downloaded from some interface into excel.
of that excel we saved it in csv and using task>import data from flat file .. the table is created
Now we want to filter data based on date ..
I am trying to get data using 'where ' clause but filter is not being set and a few records slipped ...
I am confused and do not know how to tackle this issue

10

sure sir but not possibel today
my client is having off Today
we can join tomorrow if you can

11

On this imported table - what is the data type of the column containing this 'date'? If you did not set it up as a datetime data type that is the problem. If it is a datetime data type, then the question remains - where do you want to display the date in that specific format?

If you are running a query and 'some' of the data is not being returned - please provide that query so we can see where there might be a problem.

Ideally, you would use a where clause such as:

WHERE datecolumn >= '2020-08-17'  --Aug 17th
AND datecolumn <  '2020-08-18'  --implicitly converts to '2020-08-18 00:00:00.000' if datecolumn is a datetime data time