[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

sql dates

When I try and insert the date into a datetime field I get.
I want to display the date as dd/mm/yyyy

, CONVERT(VARCHAR,GETDATE(),101)             AS EXTRACTION_DATE

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

i've even tried --declare @date as datetime
--SET DATEFORMAT dmy;

0
aneilg
Asked:
aneilg
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you want to insert into a datetime, you need to keep a datetime value.
varchar is not datetime.

if you want only to get rid of the time portion:
, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),101)  , 101) AS EXTRACTION_DATE

see also:
http://www.experts-exchange.com/A_1499.html
0
 
aneilgAuthor Commented:
thanks for that but the date is still 10-28-2011 00:00:00.000.

you have also ansered my next question, yes i want to keep it as a date.

I am selecting from and inseting into.

the table i am inserting into the field is set to datetiem, i have also tried smalldatetime.
0
 
aneilgAuthor Commented:
basically what i want to do is get todays date and store it in a table with the format dd/mm/yyyy

i was originally using just getdate()
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why that format?

if you read up the article, you will see that date as such does not have a format.
but, to display it, you need to convert to a format, which you can specify during select using convert.
you COULD store the date as VARCHAR, but then, in format dd/mm/yyyy you are just looking for troubles.
0
 
mcs0506Commented:
Hi
Try this one

CONVERT(VARCHAR,GETDATE(),103)             AS EXTRACTION_DATE


Regards

Dani
0
 
aneilgAuthor Commented:
good
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now