-6

I want to convert a text date to system date format. How can I accomplish that?

I have the format where I want to convert it 'MM/DD/YYYY' for example, but it can change, it can be dd/mm/yyyy or yyyy/mm/dd.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
Luis
  • 2,665
  • 8
  • 44
  • 70
  • After a while of puzzled looking I think I get an idea what you want: You have the format as a string parameter and you want to format a date according to this? Which version of SQL Server? – Shnugo Apr 13 '16 at 13:49
  • Hi Luis, I edited my answer... – Shnugo Apr 13 '16 at 13:56
  • Hi Luis, I see you've been around. I'm curious... Did I riddle out the problem you actually have? Did my answer help you? – Shnugo Apr 14 '16 at 10:44

2 Answers2

0

Here is an example

SELECT CONVERT(DATETIME,'04/13/2016',101)

The result is of datetime datatype

Madhivanan
  • 13,470
  • 1
  • 24
  • 29
  • i don't want by code, because i want to do this dynamic, only pass to the function the date format where is convert to – Luis Apr 13 '16 at 13:35
0

EDIT

From SQL Server 2012 and later you might use the FORMAT function if you want to force a date into a formatted text: https://msdn.microsoft.com/en-us/library/hh213505.aspx

With earlier versions you might use something like this:

DECLARE @d DATETIME=GETDATE();

DECLARE @TargetFormat VARCHAR(100)='DD/MM/YYYY';

SELECT CONVERT(VARCHAR(100),@d, CASE @TargetFormat
                                  WHEN 'MM/DD/YYYY' THEN 101
                                  WHEN 'DD/MM/YYYY' THEN 103
                                  --add all formats convert can undertsand
                                 ELSE 101 END )

Previous

Look here: https://msdn.microsoft.com/en-us/library/ms187928.aspx

The third parameter in CONVERT specifies the format.

The MM/DD/YYYY was 101, dd/mm/yyyy was 103

But: You should avoid date literals as they are depending on your system's culture.

From your question I get that you are the owner of the source, so you can change this to the way you need it. I strongly advise you to use independent formats.

Look here: https://stackoverflow.com/a/34275965/5089204

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • i don't want by code, because i want to do this dynamic, only pass to the function the date format where is convert to – Luis Apr 13 '16 at 13:35
  • The date format is based by the timezone (and what is used in the country). – Luis Apr 13 '16 at 13:36
  • sorry @Luis, but I don't understand you... You want to *convert a text date to system date format* Where is the text date coming from, what are you going to do with it? – Shnugo Apr 13 '16 at 13:37
  • @Luis this bound to the language. You might have a look here: http://stackoverflow.com/a/36421979/5089204 – Shnugo Apr 13 '16 at 13:38